VBA opening a file with partial name-CodePudding

I am trying to open a file which will be updated periodically. The current name is "GDE Portfolio Characteristics 12.31.2021" and the idea is to instruct the code to open it, no matter the date (i.e. the last 10 characters). I should only have one file in the folder with such a partial name.

The code I use is the following:

Workbooks.Open Filename:=ThisWorkbook.Path & "\Parametric GDE Portfolio Characteristics*.xlsx"

When running it, it seems it does not find the file. It works if I instead use the entire name of the file.

Newbie problem, but scratching my head in frustration!

Many thanks

CodePudding user response：

There is no way to use a wildcard in the Open-statement. However, you can use the Dir-command to get the real file name as it allows wildcards:

Dim fileName As String
fileName = Dir(ThisWorkbook.Path & "\Parametric GDE Portfolio Characteristics*.xlsx")
If fileName <> "" Then
    Workbooks.Open Filename:=ThisWorkbook.Path & "\" & fileName
End If

CodePudding user response：

Here is a more generic approach:

Sub OpenFiles()
    Dim Files As Collection
    Set Files = ListFiles(ThisWorkbook.Path, "Parametric GDE Portfolio Characteristics*.xlsx")

    Dim Filename As Variant
    
    For Each Filename In Files
        Workbooks.Open Filename:=Filename
    Next
End Sub

Function ListFiles(FolderName As String, SearchString As String) As Collection
    Set ListFiles = New Collection
    
    Dim Filename As String
    Filename = Dir(FolderName & "\" & SearchString)
    
    If Len(Filename) = 0 Then Exit Function
    
    Do While Filename <> ""
        ListFiles.Add Filename
        Filename = Dir()
    Loop
End Function