excel vba Wildcard file names: if StrFile = "test*.xlsx" then msgbox ("Confirmed")

Question

My VBA is not that good so apologies in advance. I have some vba code but cant get it to work based on filename with Wildcards. It just ignores the condition There are over 120 excel files but I only want xlsx files that begin with specific text an example would be All_Regions.xlsx and I am trying to get a hit from a partial filename using wildcards.

I can get it to work from a static name but the issue is each week the file name will be different: All_Regions_01012018 to All_Regions_08012018 and so on each week.

Any assistance would be greatly appreciated.

Example below:

Sub Testing()
OPCO = "All_re*.xlsx"
Source = "C:\files\All_OPCO\"
StrFile = Dir(Source)
If OPCO = StrFile Then
    MsgBox ("Confirmed")
End If

End Sub

Possible duplicate of: https://stackoverflow.com/questions/11573914/check-if-the-file-exists-using-vba — sɐunıɔןɐqɐp, Apr 24 '18 at 09:44

score 0 · Accepted Answer · answered Apr 24 '18 at 09:34

You need to include the file's search pattern within the Dir parameter and to test if it exists simply check that StrFile is not empty:

Try this:

Sub Testing()
    OPCO = "All_re*.xlsx"
    Source = "C:\files\All_OPCO\"
    StrFile = Dir(Source & OPCO)
    If StrFile<>"" Then
        MsgBox ("Confirmed")
    End If
End Sub

excel vba Wildcard file names: if StrFile = "test*.xlsx" then msgbox ("Confirmed")

1 Answers1