[VBA] How to Get Command Prompt Execution Results (Output) as a String

Introduction

While you can execute Command Prompt commands from VBA using the Shell function, there are times when you want to receive the execution result (text displayed on the screen) as a VBA variable to use within your program.

Examples include getting a list of files from the dir command or checking network connectivity from the ping command results.

To capture the Standard Output (StdOut) of a command like this, you must use the .Exec method of the WScript.Shell object instead of the .Run method.

This article explains the advanced technique of executing a command using the .Exec method and storing the result in a VBA string variable.

VBA Sample Code to Get Command Execution Results

This macro executes the dir command to get a list of files in the folder where the macro workbook is saved, and displays the result in a message box.

Complete Code

' Get Command Prompt execution results as a string
Sub GetCommandOutput()
    Dim shellObj As Object
    Dim execResult As Object
    Dim commandText As String
    Dim outputText As String
    
    '--- 1. Create WScript.Shell Object ---
    Set shellObj = CreateObject("WScript.Shell")
    
    '--- 2. Prepare the command you want to execute ---
    ' "dir" command to get a list of files in this workbook's folder
    commandText = "dir """ & ThisWorkbook.Path & """ /B"
    
    '--- 3. Execute the command with the .Exec method ---
    ' %ComSpec% /c executes the command string and then terminates
    Set execResult = shellObj.Exec("%ComSpec% /c " & commandText)
    
    '--- 4. Wait for the command to complete ---
    ' execResult.Status is 0 while running, 1 when complete
    Do While execResult.Status = 0
        DoEvents ' Release CPU to prevent Excel from freezing
    Loop
    
    '--- 5. Read Standard Output ---
    outputText = execResult.StdOut.ReadAll
    
    '--- 6. Display result and clean up ---
    MsgBox "[File List]" & vbCrLf & outputText, vbInformation, "Command Execution Result"
    Set shellObj = Nothing
    Set execResult = Nothing
End Sub

Explanation of the Code

Set execResult = shellObj.Exec(...)

Using the .Exec method instead of .Run is the most important point. .Exec returns a WshExec object that contains information about the executed command (execution status, standard input/output, etc.).

Do While execResult.Status = 0 ... Loop

Commands executed with .Exec run asynchronously (in the background) from the VBA process. If you proceed to the next line without waiting for the command to finish, you will try to read the output before it exists.

• execResult.Status: A property indicating the execution state of the command. It is 0 while running and 1 when complete.
• This Do While loop continues as long as Status is 0, waiting for the command to finish.
• DoEvents: Inserting DoEvents inside the loop gives Excel time to handle other processes (like screen repainting), preventing it from looking frozen.

outputText = execResult.StdOut.ReadAll

After the command execution is complete, you retrieve the result.

• execResult.StdOut: Accesses the Standard Output stream of the WshExec object.
• .ReadAll: Reads the entire content of the standard output as a single string at once.

Summary

In this article, I explained how to use the .Exec method of the WScript.Shell object to capture Command Prompt execution results in VBA.

1. Execute the command with the .Exec method to get a WshExec object.
2. Monitor the .Status property to wait for the command to complete.
3. Use .StdOut.ReadAll to get the content of the standard output as a string.

Mastering this technique allows you to incorporate the functionality of various Windows command-line tools into your custom macros, extending capabilities beyond standard VBA limits.