Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Run a Powershell command (not script) from Excel VBA

Tags:

powershell

vba

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.

like image 284
Tim Avatar asked Aug 19 '15 16:08

Tim


People also ask

Can VBA run PowerShell script?

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.

How do I run a ps1 file from command prompt?

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.

How do I manually run PowerShell?

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.


1 Answers

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}"""
like image 198
Bond Avatar answered Sep 17 '22 04:09

Bond