Using Workbooks.Open on a known file location yields "Run-time error 1004 could not be found"

Question

I am building a utility to grab data from specific files and transfer that data to another file. The source files all have the same naming convention which includes an identical string that can be used to differentiate them from other files in a directory. The intent is to use that string with wildcards to open the file and pull the required data.

I thought I had Workbook.Open working with wildcards yesterday, after consulting this StackOverflow post and this entry in the Office Dev Center, but today I try running my test code and VBA claims not to find the file (though the text suggests otherwise).

There is not much to go wrong in my test code:

Dim strFilePath As String
    strFilePath = "C:\...\KMMacros\DeepLinkTransferFromSabaForm\"
Dim strFileName As String
    strFileName = "*SabaEntryForm.xlsx"

Workbooks.Open FileName:=Dir$(strFilePath & strFileName), ReadOnly:=True

The error reads

'TEST_KM6.7-3_BRSTA_SabaEntryForm.xlsx' could not be found. Check the spelling of the file name, and verify that the file location is correct.

and debug highlights

Workbooks.Open FileName:=Dir$(strFilePath & strFileName), ReadOnly:=True

Because the error specifically mentions the file I expected to open, and because my code does not give the specific file name, it seems like VBA has found the file, even though the error states otherwise.

How can VBA be finding the name of the file and then claim that it cannot find it? What am I doing wrong?

Answer 1

Dir() only returns the filename, not the whole path, so if your current directory is different from the folder you pass to Dir then you will have this problem.

Dim strFilePath As String
    strFilePath = "C:\...\KMMacros\DeepLinkTransferFromSabaForm\"

Dim strFileName As String
    strFileName = "*SabaEntryForm.xlsx"

Workbooks.Open FileName:=strFilePath & Dir$(strFilePath & strFileName), _
               ReadOnly:=True