Have you ever wanted to run a PowerShell command from within VBA and get back a value?

Executing a PowerShell Command From VBA

Before getting into returning a response, let first look at simply executing a command. 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.

PowerShell can simply be called/executed from a standard command prompt using the command

powershell -command

Armed with this information when can simple use a WScript Shell object to execute the command, thus giving us a simple function along the lines of

'---------------------------------------------------------------------------------------
' Procedure : PS_Execute
' Author    : Daniel Pineault, CARDA Consultants Inc.
' Website   : http://www.cardaconsultants.com
' Purpose   : Run a PowerShell command
' Copyright : The following is release as Attribution-ShareAlike 4.0 International
'             (CC BY-SA 4.0) - https://creativecommons.org/licenses/by-sa/4.0/
' Req'd Refs: Late Binding  -> none required
'
' Input Variables:
' ~~~~~~~~~~~~~~~~
' sPSCmd : PowerShell command to run
'
' Usage:
' ~~~~~~
' Copy a File
'   PS_Execute "Copy-Item -Path C:\temp\Book1.xls -Destination C:\temp\charts\Book1.xls -Force"
' Create a Directory or Directories
'   PS_Execute "New-Item -Path 'C:\temp\charts\Test' -ItemType Directory"
'   Can do multiple directories in one call!
'   PS_Execute "New-Item -Path 'C:\temp\charts\Test\1\2\3\4' -ItemType Directory"
'
' Revision History:
' Rev       Date(yyyy-mm-dd)        Description
' **************************************************************************************
' 1         2020-04-13              Initial Release
'---------------------------------------------------------------------------------------
Public Sub PS_Execute(ByVal sPSCmd As String)
    'Setup the powershell command properly
    sPSCmd = "powershell -command " & sPSCmd
    'Execute and capture the returned value
    CreateObject("WScript.Shell").Exec (sPSCmd)
End Sub

The issue with the above is that it generates a visible PowerShell window that the end-user will see. Luckily, there a a small change we can make to avoid this. By switching from using .Exec to .Run we can silently run PowerShell commands. As such, our procedure becomes

'---------------------------------------------------------------------------------------
' Procedure : PS_Execute
' Author    : Daniel Pineault, CARDA Consultants Inc.
' Website   : http://www.cardaconsultants.com
' Purpose   : Run a PowerShell command
' Copyright : The following is release as Attribution-ShareAlike 4.0 International
'             (CC BY-SA 4.0) - https://creativecommons.org/licenses/by-sa/4.0/
' Req'd Refs: Late Binding  -> none required
'
' Input Variables:
' ~~~~~~~~~~~~~~~~
' sPSCmd : PowerShell command to run
'
' Usage:
' ~~~~~~
' Copy a File
'   PS_Execute "Copy-Item -Path C:\temp\Book1.xls -Destination C:\temp\charts\Book1.xls -Force"
' Create a Directory or Directories
'   PS_Execute "New-Item -Path 'C:\temp\charts\Test' -ItemType Directory"
'   Can do multiple directories in one call!
'   PS_Execute "New-Item -Path 'C:\temp\charts\Test\1\2\3\4' -ItemType Directory"
'
' Revision History:
' Rev       Date(yyyy-mm-dd)        Description
' **************************************************************************************
' 1         2020-04-13              Initial Release
' 2         2024-02-07              Added quotes around command string
'                                   Added ExecutionPolicy, bShowShell, bDebug arguments
'---------------------------------------------------------------------------------------
Public Sub PS_Execute(ByVal sPSCmd As String, _
                      Optional sExecutionPolicy As String, _
                      Optional bShowShell As Boolean = False, _
                      Optional bDebug As Boolean = False)
    'Setup the powershell command properly
    If sExecutionPolicy = "" Then
        sPSCmd = "powershell -command """ & sPSCmd & """"
    Else
        sPSCmd = "powershell -executionpolicy " & sExecutionPolicy & " -command """ & sPSCmd & """"    'Required to make BurntToast work
    End If
    If bDebug Then Debug.Print sPSCmd
    'Execute the command
    If bShowShell Then
        CreateObject("WScript.Shell").Run sPSCmd, 1, True    'Display to user
    Else
        CreateObject("WScript.Shell").Run sPSCmd, 0, True    'Hide from user
    End If
End Sub

Now that we know the basics, let’s look at how we could then return the response from PowerShell commands.

Returning a PowerShell Value in VBA

The next step is to return the response in the case where one is generated. Luckily for us the WScript Shell object provides us with everything we need with the StdOut (standard output) property and ReadAll Method.

'---------------------------------------------------------------------------------------
' Procedure : PS_GetOutput
' Author    : Daniel Pineault, CARDA Consultants Inc.
' Website   : http://www.cardaconsultants.com
' Purpose   : Run a PowerShell command and return the response
' Copyright : The following is release as Attribution-ShareAlike 4.0 International
'             (CC BY-SA 4.0) - https://creativecommons.org/licenses/by-sa/4.0/
' Req'd Refs: Late Binding  -> none required
'
' Input Variables:
' ~~~~~~~~~~~~~~~~
' sPSCmd : PowerShell command to run and return the value/response of
'
' Usage:
' ~~~~~~
' ? PS_GetOutput("Get-ComputerInfo -Property 'OsName'")
'   Returns
'       OsName
'       ------
'       Microsoft Windows 10 Home
'
' Revision History:
' Rev       Date(yyyy-mm-dd)        Description
' **************************************************************************************
' 1         2020-04-13              Initial Release
'---------------------------------------------------------------------------------------
Public Function PS_GetOutput(ByVal sPSCmd As String) As String
    'Setup the powershell command properly
    sPSCmd = "powershell -command " & sPSCmd
    'Execute and capture the returned value
    PS_GetOutput = CreateObject("WScript.Shell").Exec(sPSCmd).StdOut.ReadAll
End Function

Now the problem with the above is that is opens the PowerShell prompt and makes it visible to the user while the command is executed. So this is less than ideal.

Silently Retrieving the PowerShell Response

One approach to retrieving the PowerShell response is to simply output the command response to a Text file.

Originally, I tried doing so by appending the PowerShell command, like we do in DOS …

sPSCmd = "powershell -command " & sPSCmd & ">" & sFile

To my surprise the text file, when read back into memory, was a mess.

ÿþ
 
 O s N a m e                                       
 
 - - - - - -                                       
 
 M i c r o s o f t   W i n d o w s   1 0   H o m e 

It would appear that it defaulted to Unicode format. Luckily for me, PowerShell offers another way to export a file and specific the format to use. So I was able to tweak the code to get a standard ASCII text file and I was back in business. The end result was

'---------------------------------------------------------------------------------------
' Procedure : PS_GetOutput
' Author    : Daniel Pineault, CARDA Consultants Inc.
' Website   : http://www.cardaconsultants.com
' Purpose   : Run a PowerShell command and return the response
' Copyright : The following is release as Attribution-ShareAlike 4.0 International
'             (CC BY-SA 4.0) - https://creativecommons.org/licenses/by-sa/4.0/
' Req'd Refs: Late Binding  -> none required
'
' Input Variables:
' ~~~~~~~~~~~~~~~~
' sPSCmd        : PowerShell command to run and return the value/response of
' sTxtFile      : Text file to output the response to and then read from
' bDelTxtFile   : Whether to delete the generated text file or not
'
' Usage:
' ~~~~~~
'?PS_GetOutput("Get-ComputerInfo -Property 'OsName'", _
'               Environ("userprofile") & "\Desktop\PSTest.txt", False)
'?PS_GetOutput("Get-ComputerInfo -Property 'OsName'")
'   Returns
'       OsName
'       ------
'       Microsoft Windows 10 Home
'
' Revision History:
' Rev       Date(yyyy-mm-dd)        Description
' **************************************************************************************
' 1         2020-04-13              Initial Release
'---------------------------------------------------------------------------------------
Public Function PS_GetOutput(ByVal sPSCmd As String, _
                              Optional sTxtFile As String = "", _
                              Optional bDelTxtFile As Boolean = True) As String
    'If no Text file was specified create one in the Temp folder
    If sTxtFile = "" Then sTxtFile = Environ("temp") & "\PSTemp.txt"
    'Build the basic PowerShell command
    sPSCmd = "powershell -command " & sPSCmd
    'Add the Out-File so the output generates a text file
    sPSCmd = sPSCmd & " | Out-File '" & sTxtFile & "' -encoding ASCII"
    'Run the PowerShell command
    CreateObject("WScript.Shell").Run sPSCmd, 0, True
    'Retrieve the content of the generated Text file
    With CreateObject("Scripting.FileSystemObject")
        'Read the contents of the text file into memory
        PS_GetOutput = .OpenTextFile(sTxtFile).ReadAll()
        'Delete the text file if so desired
        If bDelTxtFile = True Then .DeleteFile sTxtFile
    End With
End Function

Perfecting the Function

Now the above is just fine, but it does uselessly use I/O by constantly reading/writing to the harddrive. So I though I’d present another option that the WScript Shell object offers us, which is to push the result to the clipboard. So, below is an example in which the WScript Shell object executes the PowerShell command silently (not shown to the user) and saves the result to the clipboard where we can then easily retrieve it.

'---------------------------------------------------------------------------------------
' Procedure : PS_GetOutput
' Author    : Daniel Pineault, CARDA Consultants Inc.
' Website   : http://www.cardaconsultants.com
' Purpose   : Run a PowerShell command and return the response
'               Improved version where the PS prompt is never displayed to the user
' Copyright : The following is release as Attribution-ShareAlike 4.0 International
'             (CC BY-SA 4.0) - https://creativecommons.org/licenses/by-sa/4.0/
' Req'd Refs: Late Binding  -> none required
'
' Input Variables:
' ~~~~~~~~~~~~~~~~
' sPSCmd : PowerShell command to run and return the value/response of
'
' Usage:
' ~~~~~~
' ? PS_GetOutput("Get-ComputerInfo -Property 'OsName'")
'   Returns
'       OsName
'       ------
'       Microsoft Windows 10 Home
'
' Revision History:
' Rev       Date(yyyy-mm-dd)        Description
' **************************************************************************************
' 1         2020-11-05              Initial Release
'---------------------------------------------------------------------------------------
Public Function PS_GetOutput(ByVal sPSCmd As String) As String
    'Setup the powershell command properly
    sPSCmd = "powershell -command " & sPSCmd & "|clip"
    'Execute the command which is being pushed to  the clipboard
    CreateObject("WScript.Shell").Run sPSCmd, 0, True
    'Get an instance of the clipboard to capture the save value
    With CreateObject("New:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")
        .GetFromClipboard
        PS_GetOutput = .GetText(1)
    End With
End Function

So there you go, in a few lines of code we can have the full power of PowerShell available to us without needing to read and write to the hard drive.

What About Elevated Priviledges – Run As

Executing a Command with Elevated Priviledges

One thing I’ve noticed playing around with PowerShell on my PC to develop the information herein is the fact that many commands seem to need to be Run As administrator to work even though performing the same action manually does not. Example of this would be turning off my WiFi, Disabling my print Spooler; I can do this in a few click without UAC, yet through PowerShell I have to use Run As.

Long story short, I wanted to find a way to do the same in VBA, it took a little playing around to get the syntax just right ” and ‘ at the right places, but the end result is

Public Sub PS_Admin_Execute(ByVal sPSCmd As String)
'Setup the powershell command properly
    sPSCmd = "powershell -command ""Start-Process powershell -Verb runAs " & _
             "-ArgumentList '-Command " & sPSCmd & "'"""
    'Execute and capture the returned value
    CreateObject("WScript.Shell").Run sPSCmd, 0, True
End Sub

So the trick is to launch an instance of PowerShell and tell it to launch another instance with elevated proviledges and run your command.

Now the above does indeed work, but there is one little problem. Although the WScript Run window is hidden, the secondary PowerShell windows is not. Once again, thanks to the power of PowerShell there is actually an easy fix, we add the -WindowStyle argument when creating the 2nd instance. So the procedure simply becomes

Public Sub PS_Admin_Execute(ByVal sPSCmd As String)
'Setup the powershell command properly
    sPSCmd = "powershell -command ""Start-Process powershell -Verb runAs -WindowStyle Hidden " & _
             "-ArgumentList '-Command " & sPSCmd & "'"""
    'Execute and capture the returned value
    CreateObject("WScript.Shell").Run sPSCmd, 0, True
End Sub

Then, with the above, you can then simply do, for instance:

Stop/Start the Print Spooler

PS_Admin_Execute "Stop-Service -Name spooler"
PS_Admin_Execute "Start-Service -Name spooler"

Disable/Enable the WiFi Adapter

PS_Admin_Execute "get-netadapter wi-fi |disable-netadapter -confirm:$false"
PS_Admin_Execute "get-netadapter wi-fi |enable-netadapter"

Getting the Output of a Command Run with Elevated Priviledges

Similarily to the other case, we can use the clipboard with the runAs Verb argument. As such we would end up with something like

Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)

Public Function PS_Admin_GetOutput(ByVal sPSCmd As String) As String
Dim i As Long

'Setup the powershell command properly
    sPSCmd = "powershell -command ""Start-Process powershell -Verb runAs -WindowStyle Hidden " & _
             "-ArgumentList '-Command " & sPSCmd & "|clip'"""
    'Execute and capture the returned value
    CreateObject("WScript.Shell").Run sPSCmd, 0, True
    With CreateObject("New:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")
        .GetFromClipboard
        'Wait for a value to appear in the clipboard, remember PowerShell is slow!
        On Error Resume Next
        For i = 0 To 100
            PS_Admin_GetOutput = .GetText(1)
            If Err.Number <> 0 Then
                Err.Clear
            Else
                Exit For
            End If
            Sleep 100 'Don't forget the API declaration!
        Next i
    End With
End Function

Do notice I’ve had to add a loop with a sleep/delay to wait for PowerShell instance to be created, run the command and return its response.

The above function can then be used by doing

Get the OS Name

? PS_Admin_GetOutput("Get-ComputerInfo -Property ""OsName""")

The UAC in All of This?!

The above works fine, but do note that you do get a UAC prompt that you have to authorize for the command to execute and I’ve found no way around this. It’s a security thing!

Because of this issue, the UAC prompt, I’d advise against using this Run As approach universally. Use the original standard code for everything you can so your users don’t have to deal with any prompts, but if ever you truly need to use commands requiring elevated permission, the code is here for you to use.

A Few Last Words On The Subject

As you can see by the code, these functions are completely application non-specific and can be used in any application that has VBA (Access, Excel, Outlook, PowerPoint, Word, …) making them that much more useful to us.

One general comment, PowerShell is slow, powerfull, but slow. As such, running PowerShell commands can cause delays when compared to other functions. So think carefully to ensure there are better alternatives. That said, PowerShell opens a whole new world of possibilities to extend beyond what we can do via VBA normally!

Also, be sure to add some error handling! What I’ve presented above works just fine, but is still bare bones and you should add proper error handling to ensure no nasty surprises for your end-users.

If you wish to do more reading regarding the Windows Shell then refer to https://docs.microsoft.com/en-us/previous-versions/windows/internet-explorer/ie-developer/windows-scripting/98591fh7(v=vs.84)

Also, I wanted to point out is that if you use the .Exec method (that is shown to the user), you can wait for the command to run and check the returned status to know if the command Finished or Failed, but it doesn’t seem to be possible to do the same thing using the .Run method?! So if you are interested in going down to this level of coding, it is possible, but you must use .Exec, check the Status property and then return the StdErr property using the ReadAll method.

Lastly, my procedures solely use the powershell command-line parameter ‘-Command‘ to make it the most versatile possible, but do note that there are many more that can be employed depending on what you are doing, most notably ‘-NoProfile‘, ‘-NonInteractive‘, ‘-OutputFormat‘, ‘-ExecutionPolicy‘. For a full listing be sure to review: about_PowerShell_exe.

I truly hope this will help a few of you out there. It was fun to explore this subject a little bit.