Putty automation using excel vba

[josechakkala]

Hi,

Some one please guide me how to automate putty using vba. I am able to invoke putty.exe using Shell command, but not able to enter the user name and password. Tried Sendkeys for entering username, but that is not working.

PuttyWindow = Shell("D:\putty.exe -telnet servername", vbNormalFocus)
AppActivate PuttyWindow, True

Application.Wait (Now + TimeValue("0:00:02"))

SendKeys "username", True
SendKeys "{ENTER}", True


Thanks in Advance
Jose

 

[josechakkala]

Please help.

 

[josechakkala]

No response Cant we access putty through excel vba.

 

[josechakkala]

Please help

 

[John_w]

As you've found, VBA SendKeys is unreliable and usually doesn't work. Also, you can't specify the window which receives the keys.

Try the following code which uses the Windows API PostMessage function to send simple character strings to a window, in this case the PuTTY window. As written, it can't send Ctrl or Alt keys or other special keys.

Main module:

Public Sub Test()

    Dim PuttyPID As Double, PuttyHwnd As Long
    Dim serverName As String, username As String, password As String
    
    serverName = "xxx.yyy"
    username = "xxxx"
    password = "xxxx"
    
    PuttyPID = Shell("C:\Program Files\PuTTY\putty.exe -telnet  " & serverName, vbNormalFocus)   'CHANGE PATH TO .EXE
    
    'Get window handle of the PuTTY Telnet command window

    PuttyHwnd = GetWindowHandle(CLng(PuttyPID))
   
    If PuttyHwnd <> 0 Then
    
        SendChars PuttyHwnd, username & vbCr
        Application.Wait DateAdd("s", 1, Now)
    
        SendChars PuttyHwnd, password & vbCr
        Application.Wait DateAdd("s", 1, Now)
    
        SendChars PuttyHwnd, "DIR" & vbCr  'DIR command
    
    End If
    
End Sub

Put this code in a separate module:

Private Const WM_CHAR As Long = &H102
Private Const WM_KEYDOWN As Long = &H100
Private Const GW_HWNDNEXT = 2

Private Declare Function FindWindow Lib "user32" Alias "FindWindowA" _
    (ByVal lpClassName As String, ByVal lpWindowName As String) As Long
    
Private Declare Function GetParent Lib "user32" _
    (ByVal hwnd As Long) As Long
    
Private Declare Function GetWindow Lib "user32" _
    (ByVal hwnd As Long, ByVal wCmd As Long) As Long
        
Private Declare Function GetWindowThreadProcessId Lib "user32" _
    (ByVal hwnd As Long, lpdwProcessId As Long) As Long

Private Declare Function PostMessage Lib "user32" Alias "PostMessageA" _
    (ByVal hwnd As Long, ByVal wMsg As Long, ByVal wParam As Long, ByVal lParam As Long) As Long


Public Sub SendChars(hwnd As Long, sChars As String)
    Dim i As Long
    Dim ret As Long
    
    For i = 1 To Len(sChars)
        ret = PostMessage(hwnd, WM_CHAR, Asc(Mid(sChars, i, 1)), 0&)
    Next
End Sub


Public Function GetWindowHandle(hInstance As Long) As Long
    Dim tempHwnd As Long
    
    ' Grab the first window handle that Windows finds:
    tempHwnd = FindWindow(vbNullString, vbNullString)
    
    ' Loop until you find a match or there are no more window handles:
    Do Until tempHwnd = 0
        ' Check if no parent for this window
        If GetParent(tempHwnd) = 0 Then
            ' Check for PID match
            If hInstance = ProcIDFromWnd(tempHwnd) Then
                ' Return found handle
                GetWindowHandle = tempHwnd
                ' Exit search loop
                Exit Do
            End If
        End If
        
        ' Get the next window handle
        tempHwnd = GetWindow(tempHwnd, GW_HWNDNEXT)
    Loop
End Function

Private Function ProcIDFromWnd(ByVal hwnd As Long) As Long
    Dim idProc As Long
    
    ' Get PID for this HWnd
    GetWindowThreadProcessId hwnd, idProc
    
    ' Return PID
    ProcIDFromWnd = idProc
End Function

 

[vtamjad]

Thank you John_w

Your code is working fine.. how to capture the session log into excel sheet

thanks in advance
amjad

 

[John_w]

First, configure PuTTY to create a session log file - Putty: Simple trick to log all session output by default - viktorious.nl.

Then put the following code in the main module, changing the puttyLogFile string as required.

Public Sub Test2()

    Dim PuttyPID As Double, PuttyHwnd As Long
    Dim serverName As String, username As String, password As String
    Dim puttyLogFile As String
        
    puttyLogFile = "C:\folder\path\putty.log"  'folder path and file name of PuTTY session log file, as configured in PuTTY
    
    serverName = "xxx.yyy"
    username = "xxxx"
    password = "xxxx"
    
    PuttyPID = Shell("C:\Program Files\PuTTY\putty.exe -telnet  " & serverName, vbNormalFocus)   'CHANGE PATH TO .EXE
    
    'Get window handle of the PuTTY Telnet command window

    PuttyHwnd = GetWindowHandle(CLng(PuttyPID))
   
    If PuttyHwnd <> 0 Then
    
        SendChars PuttyHwnd, username & vbCr
        Application.Wait DateAdd("s", 1, Now)
    
        SendChars PuttyHwnd, password & vbCr
        Application.Wait DateAdd("s", 1, Now)
    
        SendChars PuttyHwnd, "DIR" & vbCr  'DIR command
    
        Worksheets("Sheet1").Cells.Clear
        Import_File puttyLogFile, Worksheets("Sheet1").Range("A1")
    
    End If
    
End Sub


Private Sub Import_File(filePath As String, destCell As Range)
    
    With destCell.Worksheet.QueryTables.Add(Connection:="TEXT;" & filePath, Destination:=destCell)
        .TextFileColumnDataTypes = Array(1)
        .AdjustColumnWidth = False
        .Refresh False
        .Delete
    End With

End Sub

 

[Erni76]

Hello John_w,

Your code would really help me in my little project but I'm getting an error on the following line:

PuttyHwnd = GetWindowHandle(CLng(PuttyPID))

The error is: Compile error, Sub or Function not declared
and the GetWindowHandle is highlighted.
(Windows 7 64bit / Excel 2013)

Is it something missing in the declaration part?

I will appreciate any help

 

[Erni76]

Ignore above ... Apparently, I missed some code for the second Module. Silly me ... Sorry

 

[Erni76]

Hi John_w,

I've used your code from your reply to post "Putty automation using excel vba"
( https://www.mrexcel.com/forum/excel-questions/853439-putty-automation-using-excel )
and it was working perfectly fine on Excel 2013 Then I copied the whole spreadsheet
to another machine ( Same OS Windows 7 64bit but Excel 2007 ) and now it seems that
the Putty window is not receiving chars send by:

SendChars PuttyHwnd, "some commands for putty" & vbCr

Any idea what I did wrong?

Also, do you know any reliable "VBA method" for 2-way communication with the serial port?
If so I could avoid using putty for this purpose.