Capture output value from a shell command in VBA?

Solution 1:

Based on Andrew Lessard's answer, here's a function to run a command and return the output as a string -

Public Function ShellRun(sCmd As String) As String

    'Run a shell command, returning the output as a string

    Dim oShell As Object
    Set oShell = CreateObject("WScript.Shell")

    'run command
    Dim oExec As Object
    Dim oOutput As Object
    Set oExec = oShell.Exec(sCmd)
    Set oOutput = oExec.StdOut

    'handle the results as they are written to and read from the StdOut object
    Dim s As String
    Dim sLine As String
    While Not oOutput.AtEndOfStream
        sLine = oOutput.ReadLine
        If sLine <> "" Then s = s & sLine & vbCrLf
    Wend

    ShellRun = s

End Function

Usage:

MsgBox ShellRun("dir c:\")

Solution 2:

You can CreateProcess the application redirecting its StdOut to a pipe, then read that pipe directly; http://pastebin.com/CszKUpNS

dim resp as string 
resp = redirect("cmd","/c dir")
resp = redirect("ipconfig","")

Solution 3:

You could always redirect the shell output to a file, then read the output from the file.

Solution 4:

Based on Brian Burns' answer, I added passing input (using StdInput) to the executable during the call. Just in case somebody stumbles upon this and has the same need.

''' <summary>
'''   Executes the given executable in a shell instance and returns the output produced
'''   by it. If iStdInput is given, it is passed to the executable during execution.
'''   Note: You must make sure to correctly enclose the executable path or any given
'''         arguments in quotes (") if they contain spaces.
''' </summary>
''' <param name="iExecutablePath">
'''   The full path to the executable (and its parameters). This string is passed to the
'''   shell unaltered, so be sure to enclose it in quotes if it contains spaces.
''' </param>
''' <param name="iStdInput">
'''   The (optional) input to pass to the executable. Default: Null
''' </param>
Public Function ExecuteAndReturnStdOutput(ByVal iExecutablePath As String, _
                                 Optional ByVal iStdInput As String = vbNullString) _
                As String

   Dim strResult As String

   Dim oShell As WshShell
   Set oShell = New WshShell

   Dim oExec As WshExec
   Set oExec = oShell.Exec(iExecutablePath)

   If iStdInput <> vbNullString Then
      oExec.StdIn.Write iStdInput
      oExec.StdIn.Close    ' Close input stream to prevent deadlock
   End If

   strResult = oExec.StdOut.ReadAll
   oExec.Terminate

   ExecuteAndReturnStdOutput = strResult

End Function

Note: You will need to add a reference to Windows Script Host Object Model so the types WshShell and WshExec are known.
(To do this go to Extras -> References in the VBA IDE's menu bar.)

You can use the following small C# program to test your call from VBA. (If you don't have Visual Studio (Express) handy, you can follow these instructions to quickly compile it from a simple source file.):

using System;

class Program
{
   static void Main(string[] args)
   {
      // Read StdIn
      string inputText = Console.In.ReadToEnd();

      // Convert input to upper case and write to StdOut
      Console.Out.Write(inputText.ToUpper());
   }
}

In VBA you could then run the following method that should show you a message box containing "ABCDEF":

Public Sub TestStdIn()
   MsgBox ExecuteAndReturnStdOutput("C:\ConvertStdInToUpper.exe", "abcdef")
End Sub