Excel macro to open file with partial filename

Question

I have tried looking on various forums and cannot seem to find a solution that fits my needs.

I have a file "BABERs FORMULAS - 24 Jan 2017 - Rev 079 11-27.xlsm". I use a macro to save the changes to the file which adds the date, revision number and timestamp to the filename. The file is located at the following path "D:\FORMULAS".

The macro I have is as below but this then gives me a Run-time error '1004' and says to check that the spelling of the file name and verify the location is correct.

Dim fname as Variant
fname = Dir("D:\FORMULAS\BABERs FORMULAS*")

If fname <> "" then
    Workbooks.open (fname)
End If

Any ideas where I could be going wrong? Any assistance would be appreciated.

Solution

Try something like the code below:

Dim fname As Variant
Dim myPath As String

myPath = "D:\FORMULAS\"
fname = Dir(myPath & "BABERs FORMULAS*")

If fname <> "" Then
    Workbooks.Open (myPath & fname)
End If