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.
VBA Execute Shell & Read OutputVisual Basic1234567891011121314151617181920212223242526Option ExplicitSub Excel_vba_Shell_Command_Execute()' /C will execute the command and Terminate the windowMsgBox ExecShellCmd("cmd.exe /c dir d:\")End SubPublic Function ExecShellCmd(FuncExec As String) As StringDim wsh As Object, wshOut As Object, sShellOut As String, sShellOutLine As String'Create object for Shell command executionSet wsh = CreateObject("WScript.Shell")'Run Excel VBA shell command and get the output stringSet wshOut = wsh.exec(FuncExec).stdout'Read each line of output from the Shell command & Append to Final Output MessageWhile Not wshOut.AtEndOfStreamsShellOutLine = wshOut.ReadLineIf sShellOutLine <> "" ThensShellOut = sShellOut & sShellOutLine & vbCrLfEnd IfWend'Return the Output of Command PromptExecShellCmd = sShellOutEnd 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.
Next Post: How to Get Data Validation Excel Drop down List?
get Excel VBA Shell Command Output within Macro