Using a wildcard to open an excel workbook

Question

I want to use a wildcard to open a workbook stored in the same folder as my macro workbook. In the folder is a file named 302113-401yr-r01.xlsm. Here is my code:

Workbooks.Open filename:=ActiveWorkbook.Path & "\302113*.xlsm"

However, it tells me that there is no such file. Any advice?

Answer 1

We cannot open a file using a wildcard - imagine the chaos if we could!

You'll need to use Dir(ActiveWorkbook.Path & "\302113*.xlsm") to loop through the files that this returns. If there will only be one then just use this function once:

Dim sFound As String

sFound = Dir(ActiveWorkbook.Path & "\302113*.xlsm")    'the first one found
If sFound <> "" Then
    Workbooks.Open filename:= ActiveWorkbook.Path & "\" & sFound
End If

Dir Function :tech on the net