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?

James Eichele · Accepted Answer · 2010-01-26 00:36:00Z

22

Use single quotes surrounding the filename:

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

answered Jan 26, 2010 at 0:36

James Eichele

119k42 gold badges183 silver badges214 bronze badges

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

3 Comments

Ahmad Over a year ago

In general, its would be better to always use quotes when dealing with filenames in Excel

David Hayes Over a year ago

I found the same issue with dashes ('-') in the file name too. Very annoying

CarloC Over a year ago

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.

Conner · Accepted Answer · 2012-08-20 05:16:53Z

6

Replace each of the spaces with %20

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

edited Aug 20, 2012 at 5:16

Conner

31.4k8 gold badges55 silver badges75 bronze badges

answered Nov 30, 2010 at 9:15

raymon

611 silver badge1 bronze badge

3 Comments

James Eichele Over a year ago

I know this is all from a very long time ago, but I think this is a perfectly valid answer.

cowls Over a year ago

Yes this is definitely an answer to the question and not a comment, @ClaricPWI is just wrong here

GôTô Over a year ago

Yes this is an answer, not as useful as @e.James's since you might replace a lot of characters

Akshay Singh · Accepted Answer · 2015-06-15 11:19:15Z

1

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.

answered Jun 15, 2015 at 11:19

Akshay Singh

1631 silver badge7 bronze badges

Collectives™ on Stack Overflow

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

3 Answers 3

3 Comments

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related