如何在Shell中运行SSH以将值返回到VBA变量？

的WScript.Shell对象的.Exec()方法是运行一个被设计成（a）所述方法控制台命令/应用，和（b）捕获其输出经由的属性WshScriptExec返回实例。

在这一点上，为什么ssh基于命令的命令不起作用是一个谜，您的代码通常可以与控制台命令一起使用。

也就是说，还有一个不使用的原因.Exec()，当从GUI应用程序（例如VBA托管应用程序）运行时，控制台窗口始终在执行过程中可见，这可能在视觉上造成破坏。

以下替代方法（可选）允许隐藏运行命令（不显示可见窗口），但请注意，这两种方法均不直接支持捕获已调用命令的输出：

• VBA自己的Shell()函数，但是总是不变地异步执行，因此确定命令何时完成比较麻烦。

• 的WScriptShell对象的.Run()方法，其任选地允许等待的命令来完成。

要获得不可见的执行和输出捕获，您可以：

• 结合WScriptShell对象的.Run()方法
• 通过将命令的输出发送到临时文件，
• 命令完成后，将临时文件读入内存。

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

这是基于Shell()此实现启发的替代方法。

如您所见，进行Shell()同步需要付出更多的努力，并且需要使用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