Excel VBA Shell command with spaces in file path

Question

Public Sub openEntryFiles()
Dim filePath, shellCommand, agingsEntry, invenEntry As String
filePath = Range("CustomerPath").Value2 & "\files\"
agingsEntry = "agings\entry_EP.bat"
invenEntry = "inven\entry_EP.bat"

shellCommand = """%ProgramFiles(x86)%\TextPad 5\TextPad.exe"" -r -q -u """ & filePath & agingsEntry & """ -u """ & filePath & invenEntry & """"
Debug.Print shellCommand
Shell shellCommand, 0
End Sub

I am trying to write a subroutine that will run a shell command with spaces in the file path. I have done lots of research about using multiple quotes, but I still get a file not found error whenever I run the code. The debug print that is outputted to the Immediate window reads:

"%ProgramFiles(x86)%\TextPad 5\TextPad.exe" -r -q -u "\\ablsgaat002\aclwin\Clients\*****\files\agings\entry_EP.bat" -u "\\ablsgaat002\aclwin\Clients\*****\files\inven\entry_EP.bat"

Copying that string into a shell window works great, however, running it from the Shell command in VBA doesn't work. What am I doing wrong?

I think you are missing the quotes sign in the string. Shouldn't the Immediate window output the string without the " character ? — Stupid_Intern
– Stupid_Intern, Commented Mar 15, 2016 at 22:01
@Santosh Without the quotes around %ProgramFiles(x86)%\TextPad 5\TextPad.exe, the command doesn't run in a shell window. — Eli Greenberg
– Eli Greenberg, Commented Mar 15, 2016 at 22:07
It might be the percentage sign that needs escaping. If you remove them and give entire full path, does Excel VBA Shell work? — Parfait
– Parfait, Commented Mar 16, 2016 at 1:06

Susilo · Accepted Answer · 2016-03-16 03:31:21Z

2

Use Environ function to get Special Folders

pathSpecial = Environ("ProgramFiles(x86)")
shellCommand = """" & pathSpecial  & "\TextPad 5\TextPad.exe"" -r -q -u """ & filePath & agingsEntry & """ -u """ & filePath & invenEntry & """"

answered Mar 16, 2016 at 3:31

Susilo

8669 silver badges18 bronze badges

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

Collectives™ on Stack Overflow

Excel VBA Shell command with spaces in file path

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related