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

How do I run SSH in Shell to return values into VBA variable?

提问by lukehawk

I am trying to run an ssh command, and capture the output to a variable in VBA (using OpenSSH). I can get this to work fine in the command line:

我正在尝试运行 ssh 命令，并将输出捕获到 VBA 中的变量（使用 OpenSSH）。我可以让它在命令行中正常工作：

ssh user@ip python C:\Temp\Remote.py

ssh user@ip python C:\Temp\Remote.py

The results are a list of values returned to the command line window. I would like to read this into a VBA variable.

结果是返回到命令行窗口的值列表。我想将其读入 VBA 变量。

I found this, thisand this. The first two do not seem to send or recieve the command correctly, as the code gets hung up on oShell.Exec(...). The shell looks like it is executing, but it just gets hung. If I close the hanging command window, the results in VBA are blank.

我找到了这个，这个和这个。前两个似乎没有正确发送或接收命令，因为代码挂在 oShell.Exec(...) 上。外壳看起来正在执行，但它只是挂起。如果我关闭挂起的命令窗口，VBA 中的结果是空白的。

Dim sCmd As String
sCmd = "ssh user@ip python C:\Temp\Remote.py"

Dim oShell As Object
Set oShell = CreateObject("WScript.Shell")
Dim oExec As Object
Dim oOutput As Object
Set oExec = oShell.Exec(sCmd)
Set oOutput = oExec.StdOut

Dim s As String
Dim sLine As String

While Not oOutput.AtEndOfStream
    sLine = oOutput.ReadLine
    If sLine <> "" Then s = s & sLine & vbCrLf
Wend

The third seems to partially work (Using 'retVal = Shell("ssh user@ip python C:\Temp\Remote.py", vbNormalFocus)'), but I cannot get the value out. I can see the command window open, the values come back, but I get some integer back in retVal.

第三个似乎部分工作（使用'retVal = Shell("ssh user@ip python C:\Temp\Remote.py", vbNormalFocus)'），但我无法获得价值。我可以看到命令窗口打开，值又回来了，但我在 retVal 中得到了一些整数。

Any help?

有什么帮助吗？

回答by mklement0

The WScript.Shellobject's .Exec()methodis the method that is designed to (a) run a consolecommand / application, and (b) capture its outputvia properties of the WshScriptExecinstance it returns.

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

At this point it is a mystery as to why your ssh-based command doesn't work - your code generallydoes work with console commands.

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

That said, there is another reason notto use .Exec(), when run from a GUIapplication, such as a VBA-hosting application, the console window is invariably made visibleduring execution, which can be visually disruptive.

也就是说，还有另一个不使用 的原因.Exec()，当从GUI应用程序（例如 VBA 托管应用程序）运行时，控制台窗口总是在执行期间可见，这可能会破坏视觉效果。

The following alternatives (optionally) allow running a command hidden(without a visible window), but note that neither directly supports capturing outputfrom the invoked command:

以下替代方案（可选）允许运行隐藏的命令（没有可见窗口），但请注意，两者都不直接支持从调用的命令捕获输出：

• VBA's own Shell()function, which, however, invariably executes asynchronously, so it's cumbersome to determine when the command finishes.

• The WScriptShellobject's .Run()method, which optionally allows waitingfor the command to finish.

• VBA 自己的Shell()函数，然而，它总是异步执行，因此确定命令何时完成很麻烦。

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

To get both invisible execution andoutput capturing, you can:

要同时获得隐形执行和输出捕获，您可以：

• combine the WScriptShellobject's .Run()method
• with sending the command's output to a temporary file,
• and, upon command completion, read the temporary file into memory.

• 结合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

---

---

Here's an alternative based on Shell()- inspired by this implementation.

这是一个基于Shell()- 受此实现启发的替代方案。

As you can see, making Shell()synchronous requires a lot more effort and requires use of the Windows API:

如您所见，进行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

回答by S Meaden

I think you should try using the "command spec" at the start of the command. On your machine this can be obtained in VBA with VBA.Environ$("comspec")which on my my machine returns C:\WINDOWS\system32\cmd.exe

我认为您应该尝试在命令开头使用“命令规范”。在你的机器上，这可以在 VBA 中获得VBA.Environ$("comspec")，在我的机器上返回C:\WINDOWS\system32\cmd.exe

So try

所以试试

 sCmd = VBA.Environ$("comspec") & " /C " & " ssh user@ip python C:\Temp\Remote.py"

The command spec needs switches in some instances cmd switches

命令规范在某些情况下需要开关cmd 开关

I do not have Python installed so cannot test.

我没有安装 Python，所以无法测试。