Run the Powershell command (not script) from Excel VBA

Question

I searched for SO and I can find many examples of running a PowerShell script from VBA, but I can not find examples of just running a simple command.

For example, this works:

Dim retval As Variant retval = Shell("PowerShell ""C:\MyScript.ps1""", vbNormalFocus) 

But this is not so:

 Dim retval As Variant Dim pscmd As String pscmd = "PowerShell " & _ Chr(34) & "Get-ScheduledTask" & _ " -TaskName " & Chr(34) & "My Task" & Chr(34) & _ " -CimSession MYLAPTOP" & Chr(34) retval = Shell(pscmd, vbNormalFocus) Debug.Print pscmd 'pscmd = PowerShell "Get-ScheduledTask -TaskName "My Task" -CimSession MYLAPTOP" 

I know that I can write the PS command to a file, execute it as a script, and then delete the file, but this does not seem very elegant.

Answer 1

To run the built-in Powershell command, you probably have to use the -Command parameter and surround your statement with quotes. It will also be easier if you use single quotes in the command.

Try the following:

 pscmd = "PowerShell -Command ""{Get-ScheduledTask -TaskName 'My Task' -CimSession MYLAPTOP}""" 

Answer 2

Not tested, but this may be a performance factor.

 pscmd = "PowerShell -NoProfile -NoLogo -ExecutionPolicy Bypass -Command {" & _ Chr(34) & "Get-ScheduledTask" & _ " -TaskName " & Chr(34) & "My Task" & Chr(34) & _ " -CimSession MYLAPTOP" & Chr(34) & "}"