Call PowerShell script from Excel macro

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.

Solution 1:

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

• http://blogs.technet.com/b/heyscriptingguy/archive/2006/05/19/how-can-i-hide-the-command-window-when-executing-a-command-like-net-localgroup-administrators.aspx
• https://stackoverflow.com/questions/6060876/vbscript-getting-results-from-shell