CODEWITHSUNDEEP
excelplinkvba
NAC
NAC

Reputation: 33

Plink SSH access through VBA Excel and terminating the cmd prompt

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

Upvotes: 2

Views: 3773

Answers (2)

NAC
NAC

Reputation: 33

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

Upvotes: 1

cheezsteak
cheezsteak

Reputation: 2923

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

Upvotes: 2

Related Questions