get Excel VBA Shell Command Output within Macro

Call Excel VBA Shell Command Object Output

There are 2 methods to read Excel vba shell command output.

1. Write the vba shell command prompt output to a text file & read the file from VBA.
2. Create Object for executing in cmd and read its output from the object itself.

Let’s see how to do this with above mentioned methods.

Execute VBA Shell, Redirect & Get Command Prompt Output

For the first method, you can redirect the output of a VBA shell command to a output text file like this.

C:\> dir > d:\dirout.txt

If you press ‘Enter’ key after this command, the output of dir command will be written to the mentioned file. Then from VBA with the help of File operations, the content of output file can be fetched.

Execute & Get Excel VBA shell Command Output

This can be done by creating a object for Shell. Once we create an object for Excel VBA Shell command execution, then using this, we can execute and capture the output as given below.

Option Explicit
Sub Excel_vba_Shell_Command_Execute()
    ' /C will execute the command and Terminate the window
    MsgBox ExecShellCmd("cmd.exe /c dir d:\")
End Sub

Public Function ExecShellCmd(FuncExec As String) As String
    Dim wsh As Object, wshOut As Object, sShellOut As String, sShellOutLine As String
    
    'Create object for Shell command execution
    Set wsh = CreateObject("WScript.Shell")

    'Run Excel VBA shell command and get the output string
    Set wshOut = wsh.exec(FuncExec).stdout

    'Read each line of output from the Shell command & Append to Final Output Message
    While Not wshOut.AtEndOfStream
        sShellOutLine = wshOut.ReadLine
        If sShellOutLine <> "" Then
            sShellOut = sShellOut & sShellOutLine & vbCrLf
        End If
    Wend

    'Return the Output of Command Prompt
    ExecShellCmd = sShellOut
End Function

The above code is referred from this link in Stackoverflow.com. Please go through the Excel_Vba_shell_command_Execute function in the above code. Use ‘/C’ parameter in the command, so that the opened window will terminate after execution.

Once the Excel VBA shell command is executed, the output can be fetched as a string from the function ExecShellCmd. Pass your Shell command or Dos command as a parameter to this function. We get the output from the Wscript.Shell Command object as a string parameter.