Wildcard characters in a hyperlink

[JWalcott]

I am creating links onto our network throughout our database. We have close to a uniform file naming convention on our network, but on occasion files are named with slight variations. I have established a hyperlink using the exact file name as follows:


LinkName = "S:\DCG-JLF\Plan Files\" & CompanyName & " " & Plan_Type & " " & "{" & PlanFileIdentifier & "}" & "\Administration\Intro Letter.doc"

Application.FollowHyperlink LinkName, , True, True

The problem is for the slight variations in file names. How do you use wildcard characters in the hyperlink? The file will always have "S:DCG-JLF\Plan Files". Beyond that, I can only be sure of the PlanFileIdentifier, the sub folder and certain key words for the actual file name. Any help would be greatly appreciated, everywhere I have looked for this has been a dead end.

Thanks,
J

 

[mdmilner]

Have you thought about fixing the problem instead of doing a work around?
Aka, build a routine that works through the folders and renames files in proper format?

The prebuilt Access methods aren't going to allow you the flexibility of wildcard searches. Only way might be to create your own search routine that narrows down the possible options and returns a (absolute) valid path for the Hyperlink.

Here's an example of grabbing information out of folders. What you could try is a series of 'InStr' tests to determine the closest possible match using whatever logic you'd like to try. Once you get a match, return the path. This set of functions is really used by me to grab all the contents of a given folder to use -- but it's adaptable to other tasks.

Public Function ReturnAllFiles(Optional ByVal selDir As String) As Boolean
Dim DirName As String
Dim TempName, TempName2, TempName3 As String, FileNum As Integer
Dim dbs As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL, strTbl As String
Dim lnCnt As Long

strTbl = "tblFiles"

Set dbs = CurrentDb
If ObjectExists("Table", strTbl) Then
  strTbl = "tblFiles"
  strSQL = "DELETE * FROM " & strTbl
  DoCmd.RunSQL strSQL
Else
  ' Create the Table
End If
'C:\DirectoryLocation
strSQL = "SELECT * FROM tblFiles"
Set rs = dbs.OpenRecordset(strSQL, dbOpenDynaset)
    
    FileNum = FreeFile
    If selDir <> "C:\" Then
      DirName = selDir
    Else
      DirName = GetDirectory2() & "\"
      If Len(DirName) = 0 Then
        ReturnAllFiles = False
        Exit Function
      End If
    End If
    
    TempName = Dir$(DirName, vbDirectory)

    While Len(TempName)
        If (TempName <> ".") And (TempName <> "..") Then    'get rid of "." and ".."
            TempName = DirName & TempName
            lnCnt = InStr(TempName, ".xls") - 7
            TempName2 = Right(TempName, Len(TempName) - lnCnt + 1)
            'GetAttr is a built-in function
            If GetAttr(TempName) <> vbDirectory Then
                'Debug.Print TempName
                rs.AddNew
                rs.Fields(0).Value = TempName   ' full path to file
                rs.Update
            End If
        End If
        TempName = Dir$
    Wend
    
    Close #FileNum

ReturnAllFiles = True

Set rs = Nothing
Set dbs = Nothing
End Function

Public Function GetDirectory2(Optional Msg) As String

    Dim bInfo As BROWSEINFO
    Dim path As String
    Dim R As Long, x As Long

'   Root folder = Desktop
    bInfo.pidlRoot = 0&

'   Title in the dialog
    If IsMissing(Msg) Then
        bInfo.lpszTitle = "Select a folder."
    Else
        bInfo.lpszTitle = Msg
    End If
    
'   Type of directory to return
    bInfo.ulFlags = &H1

'   Display the dialog
    x = SHBrowseForFolder(bInfo)
    
'   Parse the result
    path = Space$(512)
    R = SHGetPathFromIDList(ByVal x, ByVal path)
    If R Then
          x = InStr(path, Chr$(0))
        GetDirectory2 = Left(path, x - 1)
    Else
        GetDirectory2 = ""
    End If

End Function

Mike