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 something like.

filename*esy

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

Answer 1

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.

Answer 2

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 3

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 4

I was trying this question as a function. This is the solution that ended up working for me.

Function fileName(path As String, sName As String, ext As String) As Variant

'path is Full path from root.  Can also use path = ActiveWorkbook.path & "\"
'sName is the string to search. ? and * are wildcards.  ? is for single char
'example sName = "book?" or sName ="March_*_2014*"
'ext is file extention ie .pdf .xlsm .xls? .j*

Dim file As Variant 'Store the next result of Dir
Dim fname() As String 'Dynamic Array for result set
ReDim fname(0 To 0) 
Dim i As Integer ' Counter
i = 0

' Use dir to search and store first result
fname(i) = path & Dir(path & "\" & sName & ext) 
i = i + 1

'Load next result
file = Dir 

While file <> "" 'While a file is found store that file in the array
  ReDim Preserve fname(0 To i) As String
  fname(i) = path & file
  file = Dir
Wend

fileName = Application.Transpose(fname) 'Print out array

 End Function

This works for me as a single or array function.

Answer 5

If you know that no other file contains "filename" and "esy" in that order then you can simply use

Workbooks.Open Filename:= "Filepath\filename*esy.*"

Or if you know the number of missing characters then (assuming 4 characters unknown)

Workbooks.Open Filename:= "Filepath\filename????esy.*"

I use this method to run code on files which are date & timestamped to ignore the timestamp part.