VBA Open file with wildcard knowing only extension

Question

I am trying to get Excel to open any file in the a given folder (ThisWorkbook.Path\Peach\Apple) that has .xlsm extension (there is always only 1 file). Is it possible to open it with wildcard character? I do not know the name of the file, just the extension.

If not, is there a way to do it?

Answer 1

Just ask the file system for the first matching file:

Dim path As String: path = ThisWorkbook.path & "\Peach\Apple\"

FindFirstFile = Dir$(path & "*.xlsm")

If (FindFirstFile <> "") Then 
   Workbooks.Open path & FindFirstFile
Else
   '// not found
End If

(This will not search sub-directories)

Answer 2

You mentioned that it would be nice addition to open last modified file or file with shortest name, so let's start - there's a code example how you can grab all three files (first finded, last modified, with shortest name). You can modify this as you wish (add some parameters, add error handling, return only specified, etc).

Sub Test()
   'declarations
    Dim fso As Object
    Dim folder As Object
    Dim file As Object
    Dim path As String

    Dim first_finded As Object
    Dim recently_modified As Object
    Dim shortest_name As Object
    Dim recently As Date
    Dim shortest As Long
    Dim firstFinded As Boolean

    'setting default recently date(24 hours from now) and path
    recently = DateAdd("h", -24, Now)
    path = ThisWorkbook.path & "\Peach\Apple\"

    Set fso = CreateObject("Scripting.FileSystemObject")
    Set folder = fso.GetFolder(path)


    'iteration over folder
    For Each file In folder.Files
        If file.Name Like "*.xlsm" Then

            'grab first finded .xlsm
            If Not firstFinded Then
                firstFinded = Not firstFinded
                Set first_finded = file
            End If

            'grab lastmodified .xlsm
            If file.DateLastModified > recently Then
                recently = file.DateLastModified
                Set recently_modified = file
            End If

            'grab short named .xlsm
            If shortest = 0 Or shortest > Len(file.Name) Then
                shortest = Len(file.Name)
                Set shortest_name = file
            End If
        End If
    Next

    'debug-print names
    Debug.Print first_finded.Name
    Debug.Print recently_modified.Name
    Debug.Print shortest_name.Name

    'so now you can uncomment this and open what you want
    'Call Workbooks.Open(path & recently_modified.Name)

End Sub