Plink SSH access through VBA Excel and terminating the cmd prompt

Question

I have a macro written to go through a list of IP addresses and run SSH through the plink.exe. The IP's are for network security devices.

The output, if SSH is accessible, will be "login as:" meaning that I can SSH to the device. The output from the cmd is read to a cell in excel and it is shown in a table, as that the device is accessible.

The PROBLEM is after executing the code for top IP, plink.exe window does not closes and I have to manually close the window to loop the code again to go to the next IP. If somehow, I can close the window in the program automatically so it can run through all the IP's (there are around 100 IP's).

The important part is I don't want to send any log-in info to the cmd after "login as:". I want to terminate the program and close the cmd window just after taking the output. Your help will be appreciated.

Sub SSH()
On Error GoTo ErrHandler
i = 3
j = 200
While (ActiveSheet.Cells(i, 3) <> "")

'Retrieve IP address from the cell

strCompAddress = ActiveSheet.Cells(i, 3)

Dim strShellCommand As String
Dim filename As String
Dim Run As String
Dim pointer As String

'Plink file location from a cell in different sheet
filename = Sheet1.Cells(8, 2).Value
pointer = "-ssh"

Run = filename & " " & pointer & " " & strCompAddress
Set osh = CreateObject("Wscript.Shell")
Set oEx = osh.Exec(Run)

'The output is inserted to a cell specified
strBuf = oEx.StdOut.ReadAll
ActiveSheet.Cells(j, 21) = strBuf

i = i + 1
j = j + 1
Wend
Exit Sub
ErrHandler: MsgBox "Please check the filename/address."
End Sub

cheezsteak · Answer 1 · 2015-08-21T15:12:32.453

2

The Wscript.Shell.Exec method returns a WshScriptExec object which has a Terminate method. That Terminate method is what you are want.

You can use it like this

Public Sub test()

    Dim WshShell, oExec
    Set WshShell = CreateObject("WScript.Shell")

    Set oExec = WshShell.Exec("calc")
    oExec.Terminate

    MsgBox oExec.Status

End Sub

EDIT

Ideally you would implement this like so

strBuf = oEx.StdOut.ReadAll
ActiveSheet.Cells(j, 21) = strBuf
oEx.Terminate

However. I suspect ReadAll will keep reading as long as the program is running. You want to just read one line then quit so just do that.

strBuf = oEx.StdOut.ReadLine
ActiveSheet.Cells(j, 21) = strBuf
oEx.Terminate ' now kill plink

edited Aug 21 '15 at 15:12

answered Aug 20 '15 at 21:42

cheezsteak

2,731
4
26
41

This works before I take the output but after it doesn't work after. – NAC Aug 21 '15 at 13:43
Does your original code ever reach the line `ActiveSheet.Cells(j, 21) = strBuf`? Its possible that `oEx.StdOut.ReadAll` doesn't stop reading until the application closes. – cheezsteak Aug 21 '15 at 13:59
The original code prints a "login as:" if the IP is reachable through SSH and is blank if not reachable. – NAC Aug 21 '15 at 15:06
Oh, Ok, I will try this out and let you know. Thanks. – NAC Aug 21 '15 at 15:14
The plink.exe still does not closes after terminating as per your suggestion. – NAC Aug 21 '15 at 15:17
if `oEx.Terminate` doesn't kill plink then idk what to do. – cheezsteak Aug 21 '15 at 15:41

score 1 · Accepted Answer · answered Sep 01 '15 at 15:37

I was able to figure out the problem. I had to make the application wait for a certain time before I could terminate it and hence was able to get the output as needed. Thanks for helping out.

Set oEx = osh.Exec(Run)
Application.Wait (Now + TimeValue("0:00:5"))
oEx.Terminate

Plink SSH access through VBA Excel and terminating the cmd prompt

2 Answers2