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.

You need to specify the path when opening a workbook - Workbooks.Open "D:\FORMULAS\" & fname — YowE3K
– YowE3K, Commented Jan 25, 2017 at 8:43

Shai Rado · Accepted Answer · 2017-01-25 08:48:10Z

7

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

answered Jan 25, 2017 at 8:48

Shai Rado

33.7k7 gold badges32 silver badges55 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Baber62 Over a year ago

I am linking this code to a button on the Quick Access Toolbar, however, when I change the code to suit the above coding it gave an error message as follows: Cannot run the macro 'PERSONAL.XLSB!Formula_Spreadsheet'. This macro may not be available in this workbook or all macros may be disabled.

Shai Rado Over a year ago

@Baber62 what error? Copy this entire code to your button, I ran it without getting any errros

Baber62 Over a year ago

@ Shai Radio My bad I hadn't realised that I had changed the name of the macro from Formula_Spreadsheet to FormulaOpen. The coding works fine. Thanks, appreciated.

Collectives™ on Stack Overflow

Excel macro to open file with partial filename

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related