vba: how do i open a file if i only know part of the file name?

Question

i need to open a file whose full filename i do not know

i know the file name is soemthing like

filename*esy

i know definitely that there's only one occurrence of this file in the given directory

Answer 1

If InStr(sFilename, "filename") > 0 and InStr(sFilename, "esy") > 0 Then
 'do somthing
end if

Or you can use RegEx

 Dim RE As Object, REMatches As Object 

    Set RE = CreateObject("vbscript.regexp") 
    With RE 
        .MultiLine = False 
        .Global = False 
        .IgnoreCase = True 
        .Pattern = "filename(.*)esy" 
    End With 

    Set REMatches = RE.Execute(sFilename) 
    REMatches(0) 'find match

Answer 2

There is an Application.FileSearch you can use (see below). You could use that to search for the files that match your pattern. This information taken from here.

Sub App_FileSearch_Example()

    With Application.FileSearch
        .NewSearch
        .LookIn = "c:\some_folder\"
        .FileName = "filename*esy"
        If .Execute(SortBy:=msoSortByLastModified, SortOrder:=msoSortOrderDescending) > 0 Then    
            For i1 = 1 To .FoundFiles.Count
                ' do something with matched file(s)
            Next i1

        End If

    End With    
End Sub

Answer 3

filename*esy is already a "shell ready" wildcard & if thats alway the case you can simply;

const SOME_PATH as string = "c:\rootdir\"
...
Dim file As String
file = Dir$(SOME_PATH & "filename*esy" & ".*")

If (Len(file) > 0) Then
  MsgBox "found " & file
End If

Just call (or loop until empty) file = Dir$() to get the next match.