I'm trying to call a PowerShell script from a macro in Excel.

I have seen a number of examples along the lines of:

retval = Shell("powershell ""C:\MyTest.ps1""", 1)

When I try to use this, however:

Sub Connect_01() Dim x x = Shell(“powershell.exe ""\\corp\hdq\Path with spaces\PowerShell\Modules\macro01.ps1""", 1)
End Sub 

I get a compiler error: Syntax error
If I remove the assignment, I get the error at the first pair of quote marks:
Expected: list separator or )"

As I don't have much experience with VBA, and none with the Shell() command, any help would be appreciated.

1

4 Answers

There is a second way to do this. It uses Windows Script Host's Exec method.

It has the great advantage that it can read back values from your external PowerShell, command line or whatever other command line tool you have. So you have a two-way communication between Excel and Powershell.

Excel macro

Sub RunAndGetCmd() strCommand = "Powershell -File ""C:\path\to\My PS File.ps1""" Set WshShell = CreateObject("WScript.Shell") Set WshShellExec = WshShell.Exec(strCommand) strOutput = WshShellExec.StdOut.ReadAll Msgbox strOutput
End Sub

Other working examples for a command could be

• strCommand = "ping.exe 127.0.0.1"
• strCommand = "Powershell Echo Hello World"

My PowerShell file My PS File.ps1 to demonstrate was

echo "Hello World"
$x = 1 + 1
echo $x

You can do all complex things in PowerShell as long as you echo your results from within your PowerShell file or command line tool. This means you write to StdOut (standard output). After the script has finished, Excel reads in all values with WshShellExec.StdOut.ReadAll

To ensure that paths with spaces are passed properly from Excel to PowerShell, surround the path with four double quotes "powershell -file ""C:\my path\"" "

Result in Excel

Caveats

• Contrary to Wscripts Run method, the Exec method cannot hide the command line window
• I wasn't able to get stable results when I used a loop as shown on Microsoft's MSDN article to check if the external command line tool was finished

Used resources

Try using this instead of 1. Not sure where the 1 comes from -

Sub Connect_01()
Dim x as Variant
x = Shell("POWERSHELL.EXE " & "\\corp\hdq\Path with space\PowerShell\Modules\macro01.ps1", vbNormalFocus)
End Sub

Or exclude it all together -

Sub Connect_01()
Dim x
x = Shell("POWERSHELL.EXE " & "\\corp\hdq\Path with space\PowerShell\Modules\macro01.ps1")
End Sub

5

In the OP portion of this thread, the code is missing a closing parenthesis. Hence the error, 'Expected: list separator or )" '.
In the answer that uses x=SHELL(, Try one of two things. 1) remove the spaces in front and behind &. 2) Instead of using &, try using +

1

use PowerShell full path instead just POWERSHELL.EXE, for me, the full path is C:\Windows\syswow64\WindowsPowerShell\v1.0\PowerShell.exe

Your Answer

Sign up or log in

Sign up using Google Sign up using Facebook Sign up using Email and Password Submit

Post as a guest

Post Your Answer Discard

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy