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?

Алексей Р · Answer 1 · 2021-07-26T13:37:25.553

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 '21 at 13:37

answered Jul 23 '21 at 20:25

Алексей Р

7,507
2
7
18

This opens up a PowerShell window, but nothing runs. – mjp Jul 26 '21 at 12:58
1

I tested the solutions (Win10, administrator rights), all ok. Perhaps [Running scripts is disabled on this system](https://tecadmin.net/powershell-running-scripts-is-disabled-system/) or file path is wrong – Алексей Р Jul 26 '21 at 13:20
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 Jul 26 '21 at 13:54
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). – mklement0 Jul 26 '21 at 16:08
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. – mjp Jul 27 '21 at 19:46

mklement0 · Accepted Answer · 2021-07-26T14:36:19.410

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}

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! — mjp, Jul 27 '21 at 19:45

Call Shell in VBA with spaces in the file path

2 Answers2

Addition