Page 1 of 2 12 LastLast
Results 1 to 10 of 19

Thread: Putty automation using excel vba

  1. #1
    New Member
    Join Date
    Nov 2013
    Posts
    22
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Putty automation using excel vba

    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

  2. #2
    New Member
    Join Date
    Nov 2013
    Posts
    22
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Putty automation using excel vba

    Please help.

  3. #3
    New Member
    Join Date
    Nov 2013
    Posts
    22
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Putty automation using excel vba

    No response Cant we access putty through excel vba.

  4. #4
    New Member
    Join Date
    Nov 2013
    Posts
    22
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Putty automation using excel vba

    Please help

  5. #5
    Board Regular
    Join Date
    Oct 2007
    Posts
    5,575
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Putty automation using excel vba

    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:
    Code:
    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:
    Code:
    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

  6. #6
    New Member
    Join Date
    Mar 2016
    Posts
    1
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Putty automation using excel vba

    Thank you John_w

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

    thanks in advance
    amjad

  7. #7
    Board Regular
    Join Date
    Oct 2007
    Posts
    5,575
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Putty automation using excel vba

    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.
    Code:
    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

  8. #8
    New Member Erni76's Avatar
    Join Date
    Dec 2009
    Location
    Warwick UK
    Posts
    32
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Putty automation using excel vba

    Hello John_w,

    Your code would really help me in my little project but I'm getting an error on the following line:
    Code:
    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

  9. #9
    New Member Erni76's Avatar
    Join Date
    Dec 2009
    Location
    Warwick UK
    Posts
    32
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Putty automation using excel vba

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

  10. #10
    New Member Erni76's Avatar
    Join Date
    Dec 2009
    Location
    Warwick UK
    Posts
    32
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Putty automation using excel vba

    Hi John_w,

    I've used your code from your reply to post "Putty automation using excel vba"
    ( https://www.mrexcel.com/forum/excel-...on-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:
    Code:
    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.

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •