CODEWITHSUNDEEP
excelvbashelloutlook
Cody Mayers
Cody Mayers

Reputation: 385

VBA Shell command with variables and spaces

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!

Upvotes: 0

Views: 5651

Answers (1)

CLR
CLR

Reputation: 12289

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

Upvotes: 1

Related Questions