I came across this fantastic code on DevHut by Daniel Pineault. I was doing file hashing and found Daniel’s script here:

Get a File Hash using VBA | DEVelopers HUT (devhut.net)

This in turn uses the PS_GetOutput script he explains here:

VBA – Run PowerShell Command | DEVelopers HUT (devhut.net)

And here is the script as of 4/12/2024 on Daniel’s site:

'---------------------------------------------------------------------------------------
' 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

This uses the power of the command line to execute a PowerShell command and places the (valid) output onto the clipboard and then retrieves the latest clipboard entry and returns that text from the function output.

This is fantastic and works beautifully.

However, I did run into a problem with the function when there is no output returned from the command. In this instance, the clip program doesn’t paste anything to the clipboard. This resulted in me getting either an error if the clipboard is empty, or the last string that I had copied (or the output of the last time I ran a successful PS_GetOutput command.

So in order to mitigate the problem of not receiving any output and the resulting undesirable effects, I added a couple of lines to check for this condition. Here is the updated function:

'---------------------------------------------------------------------------------------
' 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
' 2         2024-04-12              Allows for no output from PS command
'---------------------------------------------------------------------------------------
Public Function PS_GetOutput(ByVal sPSCmd As String) As String
    'Paste a known value to the clipboard in case the command has no output
    Const ClipboardKnownValue As String = "DevHut:PS_GetOutput"
    With CreateObject("New:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")
        .SetText ClipboardKnownValue
        .PutInClipboard
    End With
    '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
    If PS_GetOutput = ClipboardKnownValue Then PS_GetOutput = ""
End Function

I added the first six lines to setup a constant string and paste it into the clipboard.

Then I check to see in the very last line if the function is equal to the known value. If so we know that there was no output captured to the clipboard. This will prevent others from experiencing a strange and difficult to diagnose issue with some commands.

Thanks Daniel, for your contributions!