Using Application.Run in Excel VBA when workbook name contains spaces

Question

Application.Run "MyWorkBook.xls!Macro1"

will work (run the macro called Macro1 in the MyWorkBook.xls file).

Application.Run "My Work Book.xls!Macro1"

will not work (if the workbook name contains spaces, Excel says "the macro cannot be found").

I just spent hours figuring out that it's the spaces causing this problem...

Is there a way around this without renaming the file?

score 22 · Accepted Answer · answered Jan 26 '10 at 00:36

22

Use single quotes surrounding the filename:

Application.Run "'My Work Book.xls'!Macro1"

answered Jan 26 '10 at 00:36

e.James

In general, its would be better to always use quotes when dealing with filenames in Excel – Ahmad Nov 16 '10 at 11:46
I found the same issue with dashes ('-') in the file name too. Very annoying – David Hayes Aug 16 '11 at 15:05
I have found the same applies to the Macro name if it has underscores (_) in it. For some reason, this just came about with users on Windows 10. – CarloC May 08 '18 at 20:52

score 6 · Answer 2 · edited Aug 20 '12 at 05:16

6

Replace each of the spaces with %20

Application.Run "My%20Work%20Book.xls!Macro1"

edited Aug 20 '12 at 05:16

Conner

answered Nov 30 '10 at 09:15

raymon

9

I know this is all from a very long time ago, but I think this is a perfectly valid answer. – e.James Apr 13 '13 at 17:32
Yes this is definitely an answer to the question and not a comment, @ClaricPWI is just wrong here – cowls Mar 27 '14 at 13:51
Yes this is an answer, not as useful as @e.James's since you might replace a lot of characters – GôTô Jul 25 '14 at 12:40

score 1 · Answer 3 · answered Jun 15 '15 at 11:19

Here File_name is the name of the file for which you want to call your macro

Application.run "'"+File_name+"'"+"!Macro"
Single quote in between double quote followed by + File_name + single quote in between double quote + your macro name in between double quotes.

3 Answers3