Running Command Prompt Commands from VBA

As someone who works extensively in Microsoft Access and Excel, I often need to execute Command Prompt commands from within VBA. The standard WScript.Shell.Run method can run commands easily, but it does not capture or return their output.

To fill that gap, I created CP_GetOutput a VBA function that executes any Command Prompt command and retrieves its textual output, ready to be used in your VBA project.

Today I’m sharing both the code, a brief explanation of how it works and a few examples of who it can be used.
 

 

The CP_GetOutput Function

Here’s the full function ready for you to copy and paste into your own VBA application.

'---------------------------------------------------------------------------------------
' Procedure : CP_GetOutput
' Author    : Daniel Pineault, CARDA Consultants Inc.
' Website   : http://www.cardaconsultants.com
' Purpose   : Retrieve the output of a Command Prompt 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:
' ~~~~~~~~~~~~~~~~
' sCPCmd : Command to run and return the results of
'
' Usage:
' ~~~~~~
' ? CP_GetOutput("set")
'
' ? CP_GetOutput("cd C:\Users && Dir") 'Switch into a desired folder and return a listing of Files/Folders
'       Returns -> file/folder listing for that directory
'
' ? CP_GetOutput("nslookup google.ca")
'       Returns -> server/address information
'
' ? CP_GetOutput("net user")
'
' ? CP_GetOutput("tasklist")
'       Returns -> List of active processes
'
' ? CP_GetOutput("netstat -an")
'       Returns -> Lists active network connections and listening ports
'
' ? CP_GetOutput("ping ServerName")
'       Returns -> Tests connectivity to a host
'
' ? CP_GetOutput("ipconfig")
'       Returns -> Shows your IP address and network config
'
' ? CP_GetOutput("ipconfig /flushdns")
'
' ? CP_GetOutput("wmic logicaldisk get name,volumename") ' Disk info
' ? CP_GetOutput("wmic cpu get name") ' CPU info
' ? CP_GetOutput("wmic memorychip get capacity,speed") ' RAM info
' ? CP_GetOutput("wmic product where ""name like '%%Office%%'"" get name,version") ' Software info

'
' ? CP_GetOutput("systeminfo")
' ? CP_GetOutput("systeminfo | find ""OS Name""")
'
' ? CP_GetOutput("whoami")
' ? CP_GetOutput("hostame") ' Computer Name
' ? CP_GetOutput("for /f ""tokens=2 delims=="" %i in ('set ^| find ""USERNAME=""') do @echo %i") ' Username, can be spoofed
'
' ? CP_GetOutput("netsh wlan show profiles") 'List WiFi Network
' ? CP_GetOutput("netsh wlan show profile name=""Michel"" key=clear") 'Get info including password for a specific network
'
' ? CP_GetOutput("tar -xf ""C:\Temp\cut.zip"" -C ""C:\Temp\Extracted""") 'Unzip a zip file
'
' ? CP_GetOutput("powercfg /getactivescheme")
'
' Revision History:
' Rev       Date(yyyy-mm-dd)        Description
' **************************************************************************************
' 1         2020-11-16              Initial Release
'---------------------------------------------------------------------------------------
Public Function CP_GetOutput(ByVal sCPCmd As String) As String
    Dim sResult               As String

    On Error GoTo Error_Handler

    ' Ensure we have a command to run
    If Len(Trim(sCPCmd)) = 0 Then GoTo Error_Handler_Exit

    ' Run the command and push the results to the clipboard
    CreateObject("Wscript.Shell").Run "cmd /c " & sCPCmd & "|clip", 0, True
    
    ' Retrieve the results from the clipboard
    With CreateObject("New:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")
        .GetFromClipboard
        sResult = .GetText
    End With
    
    ' Pass the CP output back to our procedure
    CP_GetOutput = sResult

Error_Handler_Exit:
    On Error Resume Next
    Exit Function

Error_Handler:
    MsgBox "The following error has occurred" & vbCrLf & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & _
           "Error Source: CP_GetOutput" & vbCrLf & _
           "Error Description: " & Err.Description & _
           Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
           , vbOKOnly + vbCritical, "An Error has Occurred!"
    Resume Error_Handler_Exit
End Function

 

How I Designed It

I designed CP_GetOutput to be simple and independent it does not require any extra references or external libraries. The key trick here is to pipe the command’s output into the clipboard and then read the clipboard’s content back into VBA.

This process works in three steps:

  • The function uses WScript.Shell to execute the command.
  • The command output is redirected to the clipboard using the |clip operator.
  • VBA reads the clipboard using a DataObject and returns the text as a string.

 

Practical Examples

Here are a few examples of how I use CP_GetOutput in my projects:

' Get your IP configuration
Debug.Print CP_GetOutput("ipconfig")
' List all active processes
Debug.Print CP_GetOutput("tasklist")
' Check network ports and listeners
Debug.Print CP_GetOutput("netstat -an")
' Test network connectivity
Debug.Print CP_GetOutput("ping google.com")
' Unzip/Extract a Zip File
Debug.Print CP_GetOutput("tar -xf ""C:\Temp\cut.zip"" -C ""C:\Temp\Extracted""") 
' Clear local DNS cache
MsgBox CP_GetOutput("ipconfig /flushdns")

 

Testing Considerations

If ever you wish to do some testing during development and see exactly how the commands are behaving in the Shell window, there are 2 things to know and do.

  • Change the 0 in the Run call to 1. This makes the windows visible
  • Chain a pause to your command, so add ‘ && pause’ to your command this will allow it to run and then wait until you dismiss the windows by pressing any key.

This is a great mechanism for development.
 

Why I Created It

I wanted an easy, flexible way to read Command Prompt output directly into VBA, without temporary files or complicated APIs. This function gives VBA direct access to system-level information, turning it into a small but powerful diagnostic tool.

I created a simple form in which I have buttons associated to the most common commands I need to run and a Text box to display the results of the command. This saves me the time of launching a command prompt, typing commands, … and it’s now part of my databases (accessible for administrators).

Note that although primarily developed to return information, it can just as easily be used to execute commands.

Whether I’m checking network configurations, verifying user accounts, unzipping a file, or automating other administrative processes, CP_GetOutput saves time and simplifies my workflow.