VBA Shell command with variables and spaces

Question

In a Microsoft Outlook macro, I'm trying to use a Shell() call to open an Excel spreadsheet (whose filepath is referenced by my templatePath variable). I keep getting syntax errors from the editor and "file not found" errors when executing it.

I started with the following line:

Shell ("""C:\Program Files (x86)\Microsoft Office\Office16\EXCEL.EXE"" ""C:\Users\My_Username\Desktop\My_Folder\Request Template.xlsx"""), vbNormalFocus

It opens the appropriate file just fine; I just don't know the proper syntax to use the templatePath variable instead of hard-coding the path to the spreadsheet. I've seen questions similar to this, but none seemed to fit my situation closely enough. Any help would be greatly appreciated!

application.TemplatesPath Will this be opened by you each time as the the username is in the path. Is the file a template? If so it may be template extension, not normal xls? — Nathan_Sav
– Nathan_Sav, Commented Mar 25, 2019 at 16:20

CLR · Accepted Answer · 2019-03-25 16:39:32Z

1

This should work:

Dim templatePath As String

templatePath = "C:\Users\My_Username\Desktop\My_Folder\RequestTemplate.xlsx"

Shell ("""C:\Program Files (x86)\Microsoft Office\Office16\EXCEL.EXE"" """ & templatePath & """"), vbNormalFocus

If your template resides in the Application.TemplatesPath and you just want to specify the filename then use:

templatePath = Application.TemplatesPath & "RequestTemplate.xlsx"

A more adjustable version:

Dim templatePath As String
Dim programPath As String
Dim templateName As String

templateName = "RequestTemplate.xlsx"
templatePath = Application.TemplatesPath
programPath = "C:\Program Files (x86)\Microsoft Office\Office16\EXCEL.EXE"

Shell ("""" & programPath & """" & " " & """" & templatePath & templateName & """"), vbNormalFocus

edited Mar 25, 2019 at 16:39

answered Mar 25, 2019 at 16:21

CLR

12.3k1 gold badge13 silver badges31 bronze badges

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

2 Comments

Cody Mayers Over a year ago

Worked perfectly! Thanks for the quick response-- all those quotation marks always confuse me.

CLR Over a year ago

When in doubt, a good way to 'proof' the quotes etc. is to send the text to the Immediate Window, instead of Shell to see what you're trying to send.

Collectives™ on Stack Overflow

VBA Shell command with variables and spaces

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related