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

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 2

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.