Call Excel VBA Shell Command Object Output
There are 2 methods to read Excel vba shell command output.
- Write the vba shell command prompt output to a text file & read the file from VBA.
- 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.