Capture output value from a shell command in VBA?
Based on the various answers mostly the one from Brian Burns, here is a shorten version, tested and functional :
Function F_shellExec(sCmd As String) As String
Dim oShell As New WshShell 'requires ref to Windows Script Host Object Model
F_shellExec = oShell.Exec(sCmd).StdOut.ReadAll
End Function
it works pretty fine and it's quite fast. BUT, if the output is too large (for example scanning the whole C: drive sCmd = "DIR /S C:\"
), ReadAll
will crash
So I came up with the 2nd solution bellow, which so far works fine, in both cases. Note that the 1st reading is faster, and that if it crash, the reading restart at the beginning, so you don't miss information
Function F_shellExec2(sCmd As String) As String
'Execute Windows Shell Commands
Dim oShell As New WshShell 'requires ref to Windows Script Host Object Model
'Dim oExec As WshExec 'not needed, but in case you need the type
Dim oOutput As TextStream
Dim sReturn As String
Dim iErr As Long
'Set oExec = oShell.Exec(sCmd) 'unused step, for the type
Set oOutput = oShell.Exec(sCmd).StdOut
On Error Resume Next
sReturn = oOutput.ReadAll
iErr = Err.Number
On Error GoTo 0
If iErr <> 0 Then
sReturn = ""
While Not oOutput.AtEndOfStream
sReturn = sReturn & oOutput.ReadLine & Chr(10)
Wend
End If
F_shellExec2 = sReturn
End Function
Regarding the reference to
Windows Script Host Object Model
: You will need to add a reference toWindows Script Host Object Model
so the typesWshShell
(andWshExec
) are known.
(To do this go to Tools -> References in the VBA IDE's menu bar.)
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 typesWshShell
andWshExec
are known.
(To do this go to Tools -> 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
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","")
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:\")