Excel VBA: Application.Run Error 1004: Cannot find

Question

Using VBA in one workbook, I'm trying to run a macro in another workbook using Application.Run "'MyFile v0.1.xls'!ABCMacro".
From reading, I believe the syntax is correct (included single quotation marks etc) but I get a 1004 run-time error that states (most times) Method 'Run' of object '_Application' failed but on other occasions has stated (I couldn't get the 1004 message to show this right now so the following is just from memory) Cannot find the file. Has it been moved or deleted?

The target workbook and the current one are in the same directory (and I cut and paste the names to avoid typos). I have also tried Application.Run using the full path back to C:\\ but with the same result.

Much has been written around this issue but none of the answers found (so far after two hours of looking and testing) actually solve the problem.

Any help would be much appreciated.

Cheers, BJA

Answer 1

I got it to happen on my system too, and after playing around with security settings and code location, I can't find what the problem is...

However, opening the file, running the macro, and closing the file makes it work fine.

Try :

Set wbToClose = Workbooks.Open("C:\yourPath\yourFile.xlsm")
Application.Run ("yourFile.xlsm!'yourFunction'")
wbToClose.Close False