The WScript.Shell object's .Exec() method is the method that is designed to (a) run a console command / application, and (b) capture its output via properties of the WshScriptExec instance it returns.
At this point it is a mystery as to why your ssh-based command doesn't work - your code generally does work with console commands.
That said, there is another reason not to use .Exec(), when run from a GUI application, such as a VBA-hosting application, the console window is invariably made visible during execution, which can be visually disruptive.
The following alternatives (optionally) allow running a command hidden (without a visible window), but note that neither directly supports capturing output from the invoked command:
VBA's own Shell() function, which, however, invariably executes asynchronously, so it's cumbersome to determine when the command finishes.
The WScriptShell object's .Run() method, which optionally allows waiting for the command to finish.
To get both invisible execution and output capturing, you can:
- combine the
WScriptShell object's .Run() method
- with sending the command's output to a temporary file,
- and, upon command completion, read the temporary file into memory.
Dim cmd as String
Dim exitCode As Integer
Dim tempFile As String
Dim capturedOutput As String
' Construct the name of a temporary file to capture the command's stdout output in.
tempFile = Environ$("TEMP") & "\" & CreateObject("Scripting.FileSystemObject").GetTempName()
' Define the command to invoke.
cmd = "ssh user@ip python C:\Temp\Remote.py"
' Use .Run() to invoke the command.
' - In order to use output redirection, the command must be prefixed with 'cmd /c '.
' - Setting the last argument to `True` makes the invocation synchronous.
' - Replace vbNormalFocus with vbHidden to run the command *invisibly*.
exitCode = CreateObject("WScript.Shell").Run("cmd /c " & cmd & " >" & tempFile, vbNormalFocus, True)
If exitCode = 0 Then ' Command succeeded.
' Read the output file, then delete the temporary file.
capturedOutput = CreateObject("Scripting.FileSystemObject").OpenTextFile(tempFile).ReadAll()
CreateObject("Scripting.FileSystemObject").DeleteFile(tempFile)
' Display the output.
MsgBox "Captured Output:" & vbNewLine & capturedOutput
Else ' Command indicated failure.
MsgBox "An unexpected error occurred.", vbExclamation
End If
Here's an alternative based on Shell() - inspired by this implementation.
As you can see, making Shell() synchronous requires a lot more effort and requires use of the Windows API:
Private Declare Function OpenProcess Lib "kernel32.dll" (ByVal dwDesiredAccessas As Long, ByVal bInheritHandle As Long, ByVal dwProcId As Long) As Long
Private Declare Function CloseHandle Lib "kernel32.dll" (ByVal hObject As Long) As Long
Private Declare Function WaitForSingleObject Lib "kernel32.dll" (ByVal hHandle As Long, ByVal dwMilliseconds As Long) As Long
Private Declare Function GetExitCodeProcess Lib "kernel32.dll" (ByVal hProcess As Long, ByRef lpExitCodeOut As Long) As Integer
Sub Main()
Dim cmd As String
Dim taskId As Integer
Dim exitCode As Integer
Dim tempFile As String
Dim capturedOutput As String
' Construct the name of a temporary file to capture the command's stdout output in.
tempFile = Environ$("TEMP") & "\" & CreateObject("Scripting.FileSystemObject").GetTempName()
' Define the command to run.
cmd = "ssh user@ip python C:\Temp\Remote.py"
' Use the SyncShell() helper function defined below to invoke the command
' synchronously and to obtain its exit code.
' - In order to use output redirection, the command must be prefixed with 'cmd /c '.
' - Add a 3rd argument with a timeout value in seconds if you don't want to wait
' indefinitely for the process to complete.
' - Replace vbNormalFocus with vbHidden to run the command *invisibly*.
exitCode = SyncShell("cmd /c " & cmd & " >" & tempFile, vbNormalFocus)
If exitCode = 0 Then ' Command succeeded.
' Read the output file and delete the temporary file.
capturedOutput = CreateObject("Scripting.FileSystemObject").OpenTextFile(tempFile).ReadAll()
CreateObject("Scripting.FileSystemObject").DeleteFile (tempFile)
' Display the output.
MsgBox "Captured Output:" & vbNewLine & capturedOutput
Else ' Command indicated failure.
MsgBox "An unexpected error occurred.", vbExclamation
End If
End Sub
' Helper function
Private Function SyncShell(ByVal cmd As String, Optional ByVal windowStyle As VbAppWinStyle = vbMinimizedFocus, Optional ByVal timeoutInSecs As Double = -1) As Long
Dim pid As Long ' PID (Process ID) as returned by Shell().
Dim h As Long ' Process handle
Dim sts As Long ' WinAPI return value
Dim timeoutMs As Long ' WINAPI timeout value
Dim exitCode As Long
' Invoke the command (invariably asynchronously) and store the PID returned.
' Note that the invocation may fail.
pid = Shell(cmd, windowStyle)
' Translate the PIP into a process *handle* with the SYNCHRONIZE and PROCESS_QUERY_LIMITED_INFORMATION access rights,
' so we can wait for the process to terminate and query its exit code.
h = OpenProcess(&H100000 Or &H1000, 0, pid) ' &H100000 == SYNCHRONIZE, &H1000 == PROCESS_QUERY_LIMITED_INFORMATION
If h = 0 Then Err.Raise vbObjectError + 1024, , "Failed to obtain process handle for process with ID " & pid & "."
' Now wait for the process to terminate.
If timeoutInSecs = -1 Then
timeoutMs = &HFFFF ' INFINITE
Else
timeoutMs = timeoutInSecs * 1000
End If
sts = WaitForSingleObject(h, timeoutMs)
If sts <> 0 Then Err.Raise vbObjectError + 1025, , "Waiting for process with ID " & pid & " to terminate timed out, or an unexpected error occurred."
' Obtain the process's exit code.
sts = GetExitCodeProcess(h, exitCode) ' Return value is a BOOL: 1 for true, 0 for false
If sts <> 1 Then Err.Raise vbObjectError + 1026, , "Failed to obtain exit code for process ID " & pid & "."
CloseHandle h
' Return the exit code.
SyncShell = exitCode
End Function
