Call Shell in VBA with spaces in the file path

Question

I'm attempting to call a powershell script in an VBA macro in Excel, but run into an error due to spaces in the file path. I've seen similar questions asked where people suggest surrounding the path with double quotes, Chr(34), etc., but that hasn't worked.

Here is my code:

Path = ActiveWorkbook.Path
shell_path = Path & "\upload.ps1"
shell_script = "POWERSHELL.exe -noexit """ & shell_path & """"
Shell (shell_script)

Variable values:

Path = "Y:\File Path"
shell_path = "Y:\File Path\upload.ps1"
shell_script = "POWERSHELL.exe -noexit "Y:\File Path\upload.ps1""

Upon running Shell(shell_script), I get the following error message in powershell:

Y:\File: The term 'Y:\File' is not recognized as the name of a cmdlet, function, script file, or operable
program. Check the spelling of the name, or if a path was included, verify that the path is correct and try again.
At line:1 char:1
+ Y:\File Path\upload.ps1
+ ~~~~~~~~~~
    + CategoryInfo          : ObjectNotFound: (Y:\File:String) [], CommandNotFoundException
    + FullyQualifiedErrorId : CommandNotFoundException

It seems like the Shell command is ignoring the extra set of quotes around the file path in the shell_script string. Also, if I move the file to a path that doesn't have spaces, everything runs fine (but that isn't a long term solution). Any ideas on what to do?

mklement0 · Accepted Answer · 2021-07-26 14:36:19Z

1

Use the -file parameter of the PowerShell CLI, which makes your embedded " quoting ("") work as intended:

script_path = ActiveWorkbook.Path & "\upload.ps1"
' Note the use of -file
shell_script = "POWERSHELL.exe -noexit -file """ & script_path & """"
Shell shell_script, vbNormalFocus

^{Note: I've added argument vbNormalFocus to the Shell command in order to run the command in a normal foreground window for easier troubleshooting. Remove it again to run the window minimized - or perhaps change to vbMinmizedNoFocus so that the minimized window doesn't receive the focus.}

Without -file, -command is implied in Windows PowerShell^[1], and -command subjects your argument to another round of interpretation, as PowerShell code, after the enclosing "..." have been stripped during command-line parsing.

In other words: The command in your question is equivalent to executing
Y:\File Path\upload.ps1 - no quotes - from inside a PowerShell session, which predictably fails, because the path isn't recognized as a whole, and only the Y:\File part is interpreted as the executable.

See this answer for more information.

^{[1] By contrast, the cross-platform, install-on-demand PowerShell (Core) edition (v6+) now defaults to -file}

edited Jul 26, 2021 at 14:36

answered Jul 24, 2021 at 21:51

mklement0

453k68 gold badges729 silver badges989 bronze badges

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

2 Comments

mjp Over a year ago

adding -file and the combination of quotes to the VBA actually seems to have done the trick! it turns out there was another issue at the same time in the powershell script, but when resolved it let your answer solve the main issue. Thank you!

mklement0 Over a year ago

Glad to hear we got to the bottom of this, @mjp.

Алексей Р · Accepted Answer · 2021-07-26 13:37:25Z

1

Based on You can escape the space by using single quotations and a backtick before the space:

Shell "POWERSHELL.exe -noexit " & Replace(ActiveWorkbook.Path, " ", "` ") & "\upload.ps1"

Addition

Also you can use FSO's ShortPath() which returns short path without spaces:

Sub test2()
    short_path = CreateObject("Scripting.FileSystemObject"). _
                 Getfile(ActiveWorkbook.Path & "\upload.ps1"). _
                 ShortPath  ' smth like "C:\test\FILEPA~1\upload.ps1"
    Shell "POWERSHELL.exe -noexit " & short_path
End Sub

edited Jul 26, 2021 at 13:37

answered Jul 23, 2021 at 20:25

Алексей Р

7,6272 gold badges9 silver badges19 bronze badges

5 Comments

mjp Over a year ago

This opens up a PowerShell window, but nothing runs.

Алексей Р Over a year ago

I tested the solutions (Win10, administrator rights), all ok. Perhaps Running scripts is disabled on this system or file path is wrong

mklement0 Over a year ago

Using neither -File (-f) nor -Command (-c) with powershell.exe defaults to the latter, and it is only there that the quoting troubles arise. It is therefore much simpler - and a more direct expression of the intent - to use the -File parameter with the OP's own quoting approach (simply place -File after -noexit). That said, if spaces are the only problematic characters in the script path, your command would work, though note that - at least hypothetically - it could still break, with paths such as C:\Folder 1\R & D\foo.ps1. The -File approach handles this case correctly

mklement0 Over a year ago

Sorry, I missed the short-path solution, which indeed would work robustly too - but is a circuitous approach that involves extra, non-obvious ceremony (and unnecessary processing overhead, which probably won't matter, though).

mjp Over a year ago

Escaping the spaces didn't work for me, and this file didn't have a short path associated with it. It definitely would work if it did have a short path though, so I gave an upvote.

Collectives™ on Stack Overflow

Call Shell in VBA with spaces in the file path

2 Answers 2

2 Comments

Addition

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Addition

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related