Get a filename from a folder using a wildcard

Question

I'm completely new to VBA and had some trouble googling this problem cause variable has multiple meanings.

I am trying to open a file and assign its name to a variable. The file's name is never the same though I always download it to the same folder (one file in that folder only). The only recognizable thing about the file are 3 letters "ABC".

So far I managed to get opening the file to work but not assigning the non-standardized file name to a variable.

 Sub openwb()

 Dim wb As Workbook Dim directory As String

 directory = "D:\Users\AAA\Desktop\Practice"

 Set FSO = CreateObject("Scripting.FileSystemObject")
 Set folder = FSO.GetFolder(directory)


 For Each file In folder.Files
     If Mid(file.Name, InStrRev(file.Name, ".") + 1) = "xlsm" Then
         Workbooks.Open directory & Application.PathSeparator & file.Name
     End If
 Next file 
 End Sub

 Public Sub RecordFileName()

 Dim sPath As String, sFile As String
 Dim wb As Workbook

 sPath = "D:\Users\AAA\Desktop\Practice"
 sFile = sPath & "*ABC*"
End Sub

Answer 1

Here is a function you can use. It will return the filename you are looking for, and you can specify a file pattern if you want to, or you can omit that argument and it will assume all files.

Function GetFullFileName(sFolder As String, Optional sPattern As String = "*") As String
  Dim sFile As String

  ' ensure sFolder ends with a backslash
  If Right$(sFolder, 1) <> "\" Then sFolder = sFolder & "\"

  sFile = Dir(sFolder & sPattern)
  If sFile = "" Then
    MsgBox "NOT FOUND: " & sFolder & sPattern
    End
  End If
  GetFullFileName = sFolder & sFile

End Function

Usage:

MsgBox GetFullFileName("C:\Users\Fred\Documents")

Or

MsgBox GetFullFileName("C:\Users\Fred\Documents\", "*ABC*.xlsm")

Or

sFullFile = GetFullFileName("C:\Users\Fred\Documents\", "*ABC*.xlsm")