8

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
    In your example, that first double-quote before PowerShell.exe is not a regular double-quote, it's a left-hand "Smartquote". Replace it with a regular double-quote and see if that helps any. – Ƭᴇcʜιᴇ007 Mar 5 '14 at 14:16
14

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

enter image description here

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

0

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
  • Thanks for the reply. That executes, but the .ps1 is not run. The console flashes too fast to read the error message, so I ran the command from cmd. I get the error: "The term '\\corp\hdq\Path' is not recognized as the name of a cmdlet, function, script file, or operable program." – Number8 Mar 5 '14 at 14:13
  • Is there any way to get the error displayed on the console? – Number8 Mar 5 '14 at 14:38
  • 1
    I've run into a similar scenario when referencing an external file and had to just map my path that had a space to a drive with NET USE and then removed it at the end of the script. You've got an interesting scenario as your powershell script itself is on such a path so it's possible you could map it within the Excel macro first. You may also be able to save the output of what happens when it runs from your macro by adding something like &" >~\Desktop\macroOutput.txt" to the end of your call to the macro01.ps1 file. Let us know how that works! – Bradley Forney Mar 5 '14 at 14:58
  • @ Bradley Forney -- could you be more explicit about capturing output? Is that after the Shell() call, or part of the command passed to Shell()? My first attempt captured nothing... – Number8 Mar 7 '14 at 19:13
  • The 1 is only the numeric value behind the window style vbNormalFocus – nixda Nov 18 '14 at 13:47
-1

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
  • Can you write up a code snippit in your answer showing the correct code. I can't understand it how you've currently written it. – djsmiley2kStaysInside Mar 23 '17 at 14:06
-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

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.