Can you use multiple wildcards in the VBA Dir function?

Question

I am working on a macro to output filenames. I have a directory containing log files for an industrial batch process. Each batch is assigned a 5-digit batch number, and for each batch there is a .csv and .txt file. The filename for both files is the same and contains the batch number, for example:

XYZ 53482 20180827.csv
XYZ 53482 20180827.txt
XYZ 53483 20180828.csv
XYZ 53483 20180828.txt
XYZ 53484 20180829.csv
XYZ 53484 20180829.txt

My macro so far is:

Sub FindBatchFile()
Dim Batch As Double
Dim DirPath As String, r As Integer

Batch = InputBox("Enter Batch Number")

DirPath = Dir("C:\Data\* " & Batch & "*", vbDirectory)
r = 1
Workbooks.Add
MsgBox (DirPath)

Do Until DirPath = ""
Cells(r, 1).Value = DirPath
MsgBox (DirPath)
r = r + 1
DirPath = Dir
Loop

End Sub

This works adequately but the output contains both .csv and .txt files. Is there a way of using multiple wildcards in the Dir function (ie. include a "*.csv" criterion as well as the "*Batch*")?

Many thanks in advance!

How about DirPath = Dir("C:\Data\* " & Batch & "*.csv", vbDirectory)? — Xabier

Xabier Xabier · Accepted Answer · 2018-08-29T10:16:31

I believe the following will work as you expect it to, just added the .csv in your DirPath:

Sub FindBatchFile()
Dim Batch As Double
Dim DirPath As String, r As Integer

Batch = InputBox("Enter Batch Number")

DirPath = Dir("C:\Data\* " & Batch & "*.csv", vbDirectory)
r = 1
Workbooks.Add
MsgBox (DirPath)

Do Until DirPath = ""
    Cells(r, 1).Value = DirPath
    MsgBox (DirPath)
    r = r + 1
    DirPath = Dir
Loop

End Sub

Can you use multiple wildcards in the VBA Dir function?

1 Answers