Putty automation using excel vba

J

josechakkala

New Member
Joined
Nov 23, 2013
Messages
22
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
 
Sort by date Sort by votes
Erni76 said:
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:
Code: 
SendChars PuttyHwnd, "some commands for putty" & vbCr
Any idea what I did wrong?
Click to expand...
Here is the code I posted in https://www.mrexcel.com/forum/excel...-using-excel-vba-post4154557.html#post4154557 updated to hopefully work in both 32-bit and 64-bit Excel. But this is untested.

Module1
Rich (BB code): 
Public Sub Test()

    Dim PuttyPID As Double
    #If  VBA7 Then
    Dim PuttyHwnd As LongPtr
    #Else 
    Dim PuttyHwnd As Long
    #End  If
    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
Module2
Code: 
Option Explicit

Private Const ConsoleClassName As String = "ConsoleWindowClass"
Private Const PuTTYClassName As String = "PuTTY"

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

'https://msdn.microsoft.com/en-us/library/office/gg264421.aspx
'64-Bit Visual Basic for Applications Overview

[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=If]#If[/URL]  VBA7 Then
    'New VBA version 7 compiler, therefore >= Office 2010
    'PtrSafe means function works in 32-bit and 64-bit Office
    'LongPtr type alias resolves to Long (32 bits) in 32-bit Office, or LongLong (64 bits) in 64-bit Office
    
    Private Declare PtrSafe Function FindWindow Lib "user32" Alias "FindWindowA" _
        (ByVal lpClassName As String, ByVal lpWindowName As String) As LongPtr
        
    Private Declare PtrSafe Function GetParent Lib "user32" _
        (ByVal hwnd As LongPtr) As LongPtr
        
    Private Declare PtrSafe Function GetWindow Lib "user32" _
        (ByVal hwnd As LongPtr, ByVal wCmd As Long) As LongPtr
            
    Private Declare PtrSafe Function GetWindowThreadProcessId Lib "user32" _
        (ByVal hwnd As LongPtr, lpdwProcessId As LongPtr) As Long
        
    Private Declare PtrSafe Function PostMessage Lib "user32" Alias "PostMessageA" _
        (ByVal hwnd As LongPtr, ByVal wMsg As Long, ByVal wParam As LongPtr, ByVal lParam As LongPtr) As Long
        
[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=Else]#Else[/URL] 
    'Old VBA version 6 or earlier compiler, therefore <= Office 2007
    
    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
        
[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=End]#End[/URL]  If


[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=If]#If[/URL]  VBA7 Then
Public Sub SendChars(hwnd As LongPtr, sChars As String)
[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=Else]#Else[/URL] 
Public Sub SendChars(hwnd As Long, sChars As String)
[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=End]#End[/URL]  If
    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&)
        'ret = PostMessage(hWnd, WM_KEYDOWN, Asc(Mid(sChars, i, 1)), 0&)
    Next
End Sub

[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=If]#If[/URL]  VBA7 Then
Public Function GetWindowHandle(hInstance As Long) As Long
[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=Else]#Else[/URL] 
Public Function GetWindowHandle(hInstance As Long) As Long
[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=End]#End[/URL]  If

[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=If]#If[/URL]  VBA7 Then
    Dim tempHwnd As LongPtr
[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=Else]#Else[/URL] 
    Dim tempHwnd As Long
[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=End]#End[/URL]  If

    ' 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

[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=If]#If[/URL]  VBA7 Then
Private Function ProcIDFromWnd(ByVal hwnd As LongPtr) As Long
[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=Else]#Else[/URL] 
Private Function ProcIDFromWnd(ByVal hwnd As Long) As Long
[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=End]#End[/URL]  If
    Dim idProc As Long
    
    ' Get PID for this HWnd
    GetWindowThreadProcessId hwnd, idProc
    
    ' Return PID
    ProcIDFromWnd = idProc
End Function

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.
Click to expand...
Search this forum for something like "COM port". Also https://archive.is/u067t and http://www.thescarms.com/vbasic/commio.aspx. For the latter you need to copy and paste CommIo.bas within the CommIo.zip download file into a VBA module. The VB code should compile in VBA without any changes.
 
Upvote 0

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
Thanks for your reply :)
I appreciate that you took time to review the code and my issue.
... but I'm still a little bit confused.

Machine 1:
Windows 7 Professional (64-bit) Service Pack 1
Excel 2013 & VBA 7.1
Your original code is working perfectly on this one even:
Code: 
Dim PuttyHwnd As Long
not
Code: 
Dim PuttyHwnd As LongPtr
as you advise for this version of VBA


Machine 2:
Windows 7 Home Basic (64-bit) Service Pack 1
Excel 2007 & VBA 6.5
Your original code should be working on this one (as this is the earlier version of VBA)
but it looks like the code is not finding the correct window :(
Is this mean that here I should use:
Code: 
Dim PuttyHwnd As LongLong
?
 
Upvote 0
Sorry, I may have misunderstood your previous post. My changes are meant to allow the code to work on 32-bit and 64-bit Office installations, regardless of the Windows OS bit size.

I have tested the original code, but not the updated code, only on Windows XP and Excel 2007, which are both 32-bit. I will install PuTTY on Windows 10 and test the updated code on Excel 2016 (32-bit installation).

Erni76 said:
Machine 2:
Windows 7 Home Basic (64-bit) Service Pack 1
Excel 2007 & VBA 6.5
Your original code should be working on this one (as this is the earlier version of VBA)
but it looks like the code is not finding the correct window :(
Is this mean that here I should use:
Code: 
Dim PuttyHwnd As LongLong
?
Click to expand...
I expect your Excel installation on Machine 2 is 32-bit; is that correct? If yes, then that line should produce a compile error on Excel 2007, because the LongLong data type was introduced in VBA7 (Excel 2010 and higher). Instead, PuttyHwnd should be declared as Long in this case.

I can only suggest stepping through the GetWindowHandle function to see if it finds the PuTTY window, and to enable that I have added code to output the title of each window as it loops through the windows. I have also fixed an error in the definition of GetWindowHandle.

If GetWindowHandle fails to find the PuTTY window you could instead try calling FindPuTTYWindow (also included below) from the main routine, like this:

Code: 
PuttyHwnd = FindPuTTYWindow(serverName & " - PuTTY")
where the argument supplied is the title of the PuTTY window.



Module2
Code: 
Option Explicit

Private Const ConsoleClassName As String = "ConsoleWindowClass"
Private Const PuTTYClassName As String = "PuTTY"

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

'https://msdn.microsoft.com/en-us/library/office/gg264421.aspx
'64-Bit Visual Basic for Applications Overview

[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=If]#If[/URL]  VBA7 Then
    'New VBA version 7 compiler, therefore >= Office 2010
    'PtrSafe means function works in 32-bit and 64-bit Office
    'LongPtr type alias resolves to Long (32 bits) in 32-bit Office, or LongLong (64 bits) in 64-bit Office
    
    Private Declare PtrSafe Function FindWindow Lib "user32" Alias "FindWindowA" _
        (ByVal lpClassName As String, ByVal lpWindowName As String) As LongPtr
        
    Private Declare PtrSafe Function GetParent Lib "user32" _
        (ByVal hWnd As LongPtr) As LongPtr
        
    Private Declare PtrSafe Function GetWindow Lib "user32" _
        (ByVal hWnd As LongPtr, ByVal wCmd As Long) As LongPtr
            
    Private Declare PtrSafe Function GetWindowThreadProcessId Lib "user32" _
        (ByVal hWnd As LongPtr, lpdwProcessId As LongPtr) As Long
    
    Private Declare PtrSafe Function GetWindowText Lib "user32" Alias "GetWindowTextA" _
        (ByVal hWnd As LongPtr, ByVal lpString As String, ByVal cch As LongPtr) As Long
    
    Private Declare PtrSafe Sub Sleep Lib "kernel32" _
        (ByVal dwMilliseconds As Long)
    
    Private Declare PtrSafe Function PostMessage Lib "user32" Alias "PostMessageA" _
        (ByVal hWnd As LongPtr, ByVal wMsg As Long, ByVal wParam As LongPtr, ByVal lParam As LongPtr) As Long
        
[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=Else]#Else[/URL] 
    'Old VBA version 6 or earlier compiler, therefore <= Office 2007
    
    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 GetWindowText Lib "user32" Alias "GetWindowTextA" _
        (ByVal hWnd As Long, ByVal lpString As String, ByVal cch As Long) As Long
        
    Private Declare Sub Sleep Lib "kernel32" _
        (ByVal dwMilliseconds 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
        
[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=End]#End[/URL]  If


[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=If]#If[/URL]  VBA7 Then
Public Sub SendChars(hWnd As LongPtr, sChars As String)
[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=Else]#Else[/URL] 
Public Sub SendChars(hWnd As Long, sChars As String)
[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=End]#End[/URL]  If
    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&)
        'ret = PostMessage(hWnd, WM_KEYDOWN, Asc(Mid(sChars, i, 1)), 0&)
    Next
End Sub


[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=If]#If[/URL]  VBA7 Then
Public Function GetWindowHandle(hInstance As Long) As LongPtr
[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=Else]#Else[/URL] 
Public Function GetWindowHandle(hInstance As Long) As Long
[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=End]#End[/URL]  If

[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=If]#If[/URL]  VBA7 Then
    Dim tempHwnd As LongPtr
[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=Else]#Else[/URL] 
    Dim tempHwnd As Long
[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=End]#End[/URL]  If
    Dim strWindowTitle As String

    ' 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
    
        strWindowTitle = Space(260)
        GetWindowText tempHwnd, strWindowTitle, 260
        strWindowTitle = TrimNull(strWindowTitle) ' Remove extra null terminator
        If Trim(strWindowTitle) <> "" Then Debug.Print Trim(strWindowTitle)
        
        ' 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


[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=If]#If[/URL]  VBA7 Then
Private Function ProcIDFromWnd(ByVal hWnd As LongPtr) As Long
[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=Else]#Else[/URL] 
Private Function ProcIDFromWnd(ByVal hWnd As Long) As Long
[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=End]#End[/URL]  If
    Dim idProc As Long
    
    ' Get PID for this HWnd
    GetWindowThreadProcessId hWnd, idProc
    
    ' Return PID
    ProcIDFromWnd = idProc
End Function


Private Function TrimNull(strNullTerminatedString As String)

    Dim lngPos As Long
    
    'Remove unnecessary null terminator
    lngPos = InStr(strNullTerminatedString, Chr$(0))
    
    If lngPos Then
        TrimNull = Left$(strNullTerminatedString, lngPos - 1)
    Else
        TrimNull = strNullTerminatedString
    End If

End Function


Public Sub APISleep(milliseconds As Long)
    Sleep milliseconds
End Sub


[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=If]#If[/URL]  VBA7 Then
Public Function FindCommandWindow(windowCaption As String) As LongPtr
[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=Else]#Else[/URL] 
Public Function FindCommandWindow(windowCaption As String) As Long
[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=End]#End[/URL]  If
    FindCommandWindow = FindWindow(ConsoleClassName, windowCaption)
End Function

[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=If]#If[/URL]  VBA7 Then
Public Function FindPuTTYWindow(windowCaption As String) As LongPtr
[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=Else]#Else[/URL] 
Public Function FindPuTTYWindow(windowCaption As String) As Long
[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=End]#End[/URL]  If
    FindPuTTYWindow = FindWindow(PuTTYClassName, windowCaption)
End Function
 
Upvote 0
John_w said:
I expect your Excel installation on Machine 2 is 32-bit; is that correct?
Click to expand...
I don't know... I've been looking into it and I can't find the way
to evaluate which bit-version of Excel's I have on each machine.
Do you know how to check this?
 
Upvote 0
I'm fairly sure that your Excel 2007 is 32-bit, because Excel 2010 was the first version which allowed you to install it as either 32-bit or 64-bit.

For your Excel 2013, I don't know where it shows the version, but if it is similar to Excel 2016 then try File menu -> Account -> About Excel.

I have tested the code on Excel 2016 (32-bit) and Windows 10 (64-bit) and it works successfully.
 
Upvote 0
Hi,

Thanks for your code.
I have a generic problem when using constant sleep time
Application.Wait DateAdd("s", 1, Now)

WE have 20 environments and to start the servers when I run the macros for each environment opening it is taking random times
for ex: env 1 it takes 10 seconds, 2nd env take 20seconds , 3rd env takes 35 seconds for displaying the prompt to enter further values.

Can we have any command to implicitly wait to use in excel.

Instead of using Application.Wait DateAdd("s", 1, Now) do we have any script to run the sendkeys code based on the display of Unix screen.

Also do we have any script to copy the text present in the putty unix screens in to excel.

PLease let me know if having any idea and this will be more usefull for all the Excel users.

Thanks,
Anil Reddy.M
 
Upvote 0
Thanks for the code, works great. Is there a function to grab text from Putty, so I can update information in excel with Putty data?

Noah
 
Upvote 0
Noah - see post no. 7 in this thread. Basically, you configure PuTTY to create a default session log file, specify that file name in the code and call the Import_File procedure to import it into Excel.
 
Upvote 0
Hi,

Instead of using dumb wait time Application.Wait DateAdd("s", 1, Now)
Do we have any option to use dynamic wait time.
As we are not sure when putty screen navigates to next screen(if server is loaded then putty screens navigate to others screens slowly )

Also using static wait time is not good practice.

Thanks
Anil







John_w said:
As you've found, VBA SendKeys is unreliable and usually doesn't work. Also, you can't specify the window which receives the keys.
Click to expand...
 
Last edited by a moderator:
Upvote 0
Hi josechakkala,

Please try this code
Dim PuttyWindow
PuttyWindow = Shell("C:\Program Files\Microsoft Application Virtualization Client\sfttray.exe /launch PuTTY username@server", vbNormalFocus)
AppActivate PuttyWindow, True
Application.Wait (Now + TimeValue("0:00:05"))


And Please replace your putty location with C:\Program Files\Microsoft Application Virtualization Client\sfttray.exe /launch PuTTY this.

This Code will run Fine.

Thanks & Regards,
Jasvinder
 
Upvote 0
You must log in or register to reply here.

Similar threads

N
  • Solved
Input Value from Excel to Browser Input Boxes.
Replies
1
Views
2K
noveske
N
J
  • Solved
Vba Selenium problem - can't control popup/popover
Replies
4
Views
2K
Js Smith
J
H
  • Question Question
How to login with username and password via Microsoft Edge in VBA code
Replies
1
Views
1K
healeydm
H
G
  • Question Question
Putty Automation // Open putty from different drive
Replies
1
Views
966
rlv01
R
H
  • Question Question
Macro: Convert pdf into Excel and extract certain data to put together with file name
Replies
1
Views
3K
HeinrichPaul
H

Forum statistics

Threads
1,223,275
Messages
6,171,123
Members
452,381
Latest member
Nova88

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top