Juts a quickie. On Stackoverflow some code posted which shells a process and reads the piped output purely using Window API calls, quite impressive but seems to be tripping up on some 64-bit issue. Actually VBA developers need not wrestle Windows API on this one and can in fact use the Windows Script Host Object Model library instead.
Option Explicit
Function ShellAndGetText() As String
'* Tools -> References
'* Windows Script Host Object Model
Dim oWshShell As IWshRuntimeLibrary.WshShell
Set oWshShell = New IWshRuntimeLibrary.WshShell
Dim oWshExec As IWshRuntimeLibrary.WshExec
Dim sComSpec As String
sComSpec = Environ$("COMSPEC")
Dim sReturnText As String
Set oWshExec = oWshShell.Exec(sComSpec & " foo.exe")
While oWshExec.Status = WshRunning
DoEvents
Wend
If oWshExec.Status = WshFinished Then
'* success
sReturnText = oWshExec.StdOut
Else
'* failure
sReturnText = oWshExec.StdErr
End If
ShellAndGetText = sReturnText
End Function
A program can return both StdOut _and_ StdErr.
ReplyDeleteSub ShellAndGetText(ByVal cmd As String, StdOut As String, StdErr As String, ByRef dt As Single)
'* Tools -> References
'* Windows Script Host Object Model
Dim StartTime As Single
StartTime = Timer()
Dim oWshShell As IWshRuntimeLibrary.WshShell
Set oWshShell = New IWshRuntimeLibrary.WshShell
Dim oWshExec As IWshRuntimeLibrary.WshExec
Set oWshExec = oWshShell.Exec(cmd)
While oWshExec.Status = WshRunning
DoEvents
Wend
StdOut = oWshExec.StdOut.ReadAll
StdErr = oWshExec.StdErr.ReadAll
dt = Timer() - StartTime
End Sub
Sub test_ShellAndGetText()
Dim StdOut As String, StdErr As String, dt As Single
Call ShellAndGetText(ThisWorkbook.Path & "\my_panic.exe", StdOut, StdErr, dt)
MsgBox StdOut
MsgBox StdErr
End Sub