Menu
I've searched SO, and I can find plenty of examples of running a PowerShell script from VBA, but I can't find any examples of just running a simple command.
For example, this works:
Dim retval As Variant
retval = Shell("PowerShell ""C:\MyScript.ps1""", vbNormalFocus)
But this does not:
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 I could write the PS command to a file, execute it as a script, and then delete the file, but that does not seem to be very elegant.
284
Executing a PowerShell Command From VBA There are many instances in which we simply want to run a PowerShell command and don't need to get any response back. It turns out that it is very easy to do and very similar to the approach taken in VBA – Get Host IP Address.
ps1 files are interpreted by PowerShell, the Command Prompt (CMD) cannot work with PowerShell scripts directly. If you would like to run a PowerShell script in CMD, you'll need to execute it by calling the PowerShell process with the -File parameter, as shown below: PowerShell -File C:\TEMP\MyNotepadScript. ps1.
Click Start, type PowerShell, and then click Windows PowerShell. From the Start menu, click Start, click All Programs, click Accessories, click the Windows PowerShell folder, and then click Windows PowerShell.
To run an inline Powershell command, you'll probably need to use the -Command param and surround your statement with quotes. It'll also be easier if you use single quotes within the command.
Try this out:
pscmd = "PowerShell -Command ""{Get-ScheduledTask -TaskName 'My Task' -CimSession MYLAPTOP}"""
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
