VBA Spy++ alternative

We can interact with Windows’s windows (sic) from within VBA, even if there is not a OCX we can set a reference to and deal with the external application. The usual and ‘inconvenient’ way to do so is via the SendKeys API, but it’s not the only one option out there.
VBA can be used to explore process and windows, and once we  know the window handle (or the caption and ClassName of it),  we can focus on that component and send keys specifically to that window, via SendMessage/PostMessage
So the first step should be to detect the open windows -the ones we need to interact with- (and their children), and get them in a worksheet (or wherever you want to output).
With following code from Mark Rowlinson (www.markrowlinson.co.uk), that copied from here, all windows will be reflected, in blocks of 3 columns (handle, ClassName and Caption), very convenient to get the interaction.
In order to get the list, run GetWindows procedure.
This post is somehow related to these old posts 1,2:

Option Explicit

'Declaring the necessary API functions for both 64 and 32 bit applications.
#If VBA7 And Win64 Then
Private Declare PtrSafe Function GetClassName Lib "user32" Alias "GetClassNameA" (ByVal hWnd As LongPtr, ByVal lpClassName As String, ByVal nMaxCount As Long) As LongPtr
Private Declare PtrSafe Function GetWindowText Lib "user32" Alias "GetWindowTextA" (ByVal hWnd As LongPtr, ByVal lpString As String, ByVal cch As Long) As LongPtr

'Performs an operation on a specified file.
Private Declare PtrSafe Function ShellExecute Lib "shell32.dll" Alias "ShellExecuteA" (ByVal hWnd As LongPtr, ByVal lpOperation As String, ByVal lpFile As String, ByVal lpParameters As String, ByVal lpDirectory As String, ByVal nShowCmd As Long) As LongPtr

'Retrieves a handle to the top-level window whose class name and window name match the specified strings.
'This function does not search child windows. This function does not perform a case-sensitive search.
Private Declare PtrSafe Function FindWindow Lib "user32" Alias "FindWindowA" (ByVal lpClassName As String, ByVal lpWindowName As String) As LongPtr

'Retrieves a handle to a window whose class name and window name match the specified strings.
'The function searches child windows, beginning with the one following the specified child window.
'This function does not perform a case-sensitive search.
Private Declare PtrSafe Function FindWindowEx Lib "user32" Alias "FindWindowExA" (ByVal hWnd1 As LongPtr, ByVal hWnd2 As LongPtr, ByVal lpClassName As String, ByVal lpWindowName As String) As LongPtr

'Sends the specified message to a window or windows. The SendMessage function calls the window procedure
'for the specified window and does not parentWindowurn until the window procedure has processed the message.
Private Declare PtrSafe Function SendMessage Lib "user32" Alias "SendMessageA" (ByVal hWnd As LongPtr, ByVal wMsg As Long, ByVal wParam As LongPtr, lParam As Any) As LongPtr

'Places (posts) a message in the message queue associated with the thread that created the specified
'window and parentWindowurns without waiting for the thread to process the message.
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
#Else
Private Declare Function GetClassName Lib "user32" Alias "GetClassNameA" (ByVal hWnd As Long, ByVal lpClassName As String, ByVal nMaxCount 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 Function ShellExecute Lib "shell32.dll" Alias "ShellExecuteA" (ByVal hWnd As Long, ByVal lpOperation As String, ByVal lpFile As String, ByVal lpParameters As String, ByVal lpDirectory As String, ByVal nShowCmd As Long) As Long
Private Declare Function FindWindow Lib "user32" Alias "FindWindowA" (ByVal lpClassName As String, ByVal lpWindowName As String) As Long
Private Declare Function FindWindowEx Lib "user32" Alias "FindWindowExA" (ByVal hWnd1 As Long, ByVal hWnd2 As Long, ByVal lpClassName As String, ByVal lpWindowName As String) As Long
Private Declare Function SendMessage Lib "user32" Alias "SendMessageA" (ByVal hWnd As Long, ByVal wMsg As Long, ByVal wParam As Long, lParam As Any) As Long
Private Declare Function PostMessage Lib "user32.dll" Alias "PostMessageA" (ByVal hWnd As Long, ByVal wMsg As Long, ByVal wParam As Long, ByVal lParam As Long) As Long
#End If

'Constants used in API functions.
Private Const SW_HIDE As Long = 0
Private Const SW_SHOWNORMAL As Long = 1
Private Const SW_SHOWMAXIMIZED As Long = 3
Private Const SW_SHOWMINIMIZED As Long = 2

Private Const WM_SETTEXT = &HC
Private Const VK_RETURN = &HD
Private Const WM_KEYDOWN = &H100

Private cnt As Integer

'Used a user defined type here rather than Enum so that it works on 97
Private Type winEnum
winHandle As Integer
winClass As Integer
winTitle As Integer
winHandleClass As Integer
winHandleTitle As Integer
winHandleClassTitle As Integer
End Type

Private winOutputType As winEnum

Public Sub GetWindows()
' VBA "Clone" of Spy++ application
cnt = 0
winOutputType.winHandle = 0
winOutputType.winClass = 1
winOutputType.winTitle = 2
winOutputType.winHandleClass = 3
winOutputType.winHandleTitle = 4
winOutputType.winHandleClassTitle = 5

GetWinInfo 0&, 0, winOutputType.winHandleClassTitle
End Sub

Private Sub GetWinInfo(hParent As Long, intOffset As Integer, OutputType As Integer)
' Sub to recursively obtain window handles, classes and text
' given a parent window to search
' Written by Mark Rowlinson
' www.markrowlinson.co.uk - The Programming Emporium
' http://www.vbaexpress.com/kb/getarticle.php?kb_id=52#instr

Dim hWnd As Long, lngRet As Long, y As Integer
Dim strText As String

hWnd = FindWindowEx(hParent, 0&, vbNullString, vbNullString)
While hWnd <> 0
With ActiveSheet
Select Case OutputType
Case winOutputType.winClass
strText = String$(100, Chr$(0))
lngRet = GetClassName(hWnd, strText, 100)
.Range("a1").Offset(cnt, intOffset) = Left$(strText, lngRet)
Case winOutputType.winHandle
.Range("a1").Offset(cnt, intOffset) = hWnd
Case winOutputType.winTitle
strText = String$(100, Chr$(0))
lngRet = GetWindowText(hWnd, strText, 100)
If lngRet > 0 Then
.Range("a1").Offset(cnt, intOffset) = Left$(strText, lngRet)
Else
.Range("a1").Offset(cnt, intOffset) = "N/A"
End If
Case winOutputType.winHandleClass
.Range("a1").Offset(cnt, intOffset) = hWnd
strText = String$(100, Chr$(0))
lngRet = GetClassName(hWnd, strText, 100)
.Range("a1").Offset(cnt, intOffset + 1) = Left$(strText, lngRet)
Case winOutputType.winHandleTitle
.Range("a1").Offset(cnt, intOffset) = hWnd
strText = String$(100, Chr$(0))
lngRet = GetWindowText(hWnd, strText, 100)
If lngRet > 0 Then
.Range("a1").Offset(cnt, intOffset + 1) = Left$(strText, lngRet)
Else
.Range("a1").Offset(cnt, intOffset + 1) = "N/A"
End If
Case winOutputType.winHandleClassTitle
.Range("a1").Offset(cnt, intOffset) = hWnd
strText = String$(100, Chr$(0))
lngRet = GetClassName(hWnd, strText, 100)
.Range("a1").Offset(cnt, intOffset + 1) = Left$(strText, lngRet)
strText = String$(100, Chr$(0))
lngRet = GetWindowText(hWnd, strText, 100)
If lngRet > 0 Then
.Range("a1").Offset(cnt, intOffset + 2) = Left$(strText, lngRet)
Else
.Range("a1").Offset(cnt, intOffset + 2) = "N/A"
End If
End Select
End With

' check for children
y = cnt
Select Case OutputType
Case Is > 4: GetWinInfo hWnd, intOffset + 3, OutputType
Case Is > 2: GetWinInfo hWnd, intOffset + 2, OutputType
Case Else: GetWinInfo hWnd, intOffset + 1, OutputType
End Select

' increment by 1 row if no children found
If y = cnt Then cnt = cnt + 1

' now get next window
hWnd = FindWindowEx(hParent, hWnd, vbNullString, vbNullString)
Wend
End Sub


One more piece of code is needed, that is to get the handle from a partial caption, so knowing some part of the caption, you can get the handle:

Option Explicit

#If VBA7 Then
Private Declare PtrSafe Function SetForegroundWindow Lib "user32" (ByVal hWnd As LongPtr) As LongPtr
Private Declare PtrSafe Function FindWindow Lib "user32" Alias "FindWindowA" (ByVal lpClassName As String, ByVal lpWindowName As String) As LongPtr
Private Declare PtrSafe Function FindWindowEx Lib "user32" Alias "FindWindowExA" (ByVal hWnd1 As LongPtr, ByVal hWnd2 As LongPtr, ByVal lpsz1 As String, ByVal lpsz2 As String) As LongPtr
Private Declare PtrSafe Function GetClassName Lib "user32" Alias "GetClassNameA" (ByVal hWnd As LongPtr, ByVal lpClassName As String, ByVal nMaxCount As Long) As LongPtr
Private Declare PtrSafe Function GetWindowText Lib "user32" Alias "GetWindowTextA" (ByVal hWnd As LongPtr, ByVal lpString As String, ByVal cch As Long) As LongPtr
Private Declare PtrSafe Function GetWindowTextLength Lib "user32" Alias "GetWindowTextLengthA" (ByVal hWnd As LongPtr) As Long
Private Declare PtrSafe Function GetWindow Lib "user32" (ByVal hWnd As LongPtr, ByVal wCmd As LongPtr) As LongPtr
Private Declare PtrSafe Function SendMessage Lib "user32" Alias "SendMessageA" (ByVal hWnd As LongPtr, ByVal wMsg As Long, ByVal wParam As Long, lParam As Any) As LongPtr
#Else
Private Declare Function SetForegroundWindow Lib "user32" (ByVal hWnd As Long) As Long
Private Declare Function FindWindow Lib "user32" Alias "FindWindowA" (ByVal lpClassName As String, ByVal lpWindowName As String) As Long
Private Declare Function FindWindowEx Lib "user32" Alias "FindWindowExA" (ByVal hWnd1 As Long, ByVal hWnd2 As Long, ByVal lpsz1 As String, ByVal lpsz2 As String) As Long
Private Declare Function GetClassName Lib "user32" Alias "GetClassNameA" (ByVal hWnd As Long, ByVal lpClassName As String, ByVal nMaxCount 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 Function GetWindowTextLength Lib "user32" Alias "GetWindowTextLengthA" (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 SendMessage Lib "user32" Alias "SendMessageA" (ByVal hWnd As Long, ByVal wMsg As Long, ByVal wParam As Long, lParam As Any) As Long
#End If

' Used a user defined type here rather than Enum so that it works on 97
Private Type winEnum
winHandle As Integer
winClass As Integer
winTitle As Integer
winHandleClass As Integer
winHandleTitle As Integer
winHandleClassTitle As Integer
End Type
Dim winOutputType As winEnum

Private Const GW_HWNDNEXT As Long = 2
' { WM_*: Window messsages
Public Const WM_CHAR = &H102
Public Const WM_CLOSE = &H10
Public Const Wm_CREATE = &H1
Public Const WM_DESTROY = &H2
Public Const WM_KEYDOWN = &H100
Public Const WM_KEYUP = &H101
Public Const WM_PAINT = &HF
Public Const WM_SETTEXT = &HC
Public Const WM_SETTINGCHANGE = &H1A
Public Const WM_SIZE = &H5
Public Const WM_SYSKEYDOWN = &H104
Public Const WM_SYSKEYUP = &H105
' }

Private sCaption As String

#If VBA7 Then
Dim lhWnd As LongPtr
#Else
Dim lhWnd As Long
#End If

#If VBA7 Then
Private Function GetHandleFromPartialCaption(ByRef hWnd As LongPtr, ByRef PartialCaption As String) As Boolean
#Else
Private Function GetHandleFromPartialCaption(ByRef hWnd As Long, ByRef PartialCaption As String) As Boolean
#End If
Dim sStr As String

GetHandleFromPartialCaption = False
lhWnd = FindWindow(vbNullString, vbNullString) 'PARENT WINDOW
Do While lhWnd <> 0
sStr = String(GetWindowTextLength(lhWnd) + 1, Chr$(0))
GetWindowText lhWnd, sStr, Len(sStr)
sStr = Left$(sStr, Len(sStr) - 1)
If InStr(1, sStr, PartialCaption) > 0 Then
PartialCaption = sStr
GetHandleFromPartialCaption = True
hWnd = lhWnd
Exit Do
End If
lhWnd = GetWindow(lhWnd, GW_HWNDNEXT)
Loop
End Function



There is some code and links below, that came from MyEngineeringWorld blog, where Christos Samaras further shows how to interact with a child window once located, that is the reason that to be posted here:
  '-------------
' some samples on how to use the hWnd and ClassName from the above code combined with "SendMessage/PostMessage" so no need for "SendKeys":
' https://myengineeringworld.net/2018/04/open-password-protected-pdf-vba.html
' https://myengineeringworld.net/2013/04/open-pdf-file-with-vba.html
' https://myengineeringworld.net/2013/04/save-web-pages-as-pdf-files.html

Private Sub OpenLockedPdf(pdfPath As String, password As String)
'------------------------------------------------------------------------
'Opens a password-protected PDF file, given its (known) password.
'API functions are used to find the pop-up window and fill the password.
'
'The subroutine can be used in every Office application, as well as
'in AutoCAD. It works for both 32 and 64 bit applications.

'The macro also works with PDF files that are NOT password-protected.
'In that case, the code after the line of ShellExecute is ignored.

'Written By: Christos Samaras
'Date: 30/04/2018
'E-mail: xristos.samaras@gmail.com
'Site: http://www.myengineeringworld.net
'------------------------------------------------------------------------

' Declaring the necessary variables (different for 32 or 64 bit applications).
#If VBA7 And Win64 Then
Dim parentWindow As LongPtr
Dim firstChildWindow As LongPtr
Dim secondChildFirstWindow As LongPtr
#Else
Dim parentWindow As Long
Dim firstChildWindow As Long
Dim secondChildFirstWindow As Long
#End If
Dim timeCount As Date

' Check if the PDF file exists.
If FileExists(pdfPath) = False Then
MsgBox "The PDF file doesn't exist!", vbCritical, "Error in PDF path"
Exit Sub
End If

'The ShellExecute API will try to open the PDF file using the default application that
'is associated with PDF files (either Adobe Reader or Professional).
ShellExecute Application.hWnd, "Open", pdfPath, vbNullString, "C:\", SW_SHOWNORMAL

'Note: The code below will be ignored if the PDF file has no protection.

'Find the handle of the pop-up window.
timeCount = Now()
Do Until Now() > timeCount + TimeValue("00:00:05")
parentWindow = 0
DoEvents
parentWindow = FindWindow("#32770", "Password")
If parentWindow <> 0 Then Exit Do
Loop

If parentWindow <> 0 Then
'Find the handle of the first child window (it is a group box).
timeCount = Now()
Do Until Now() > timeCount + TimeValue("00:00:05")
firstChildWindow = 0
DoEvents
firstChildWindow = FindWindowEx(parentWindow, ByVal 0&, "GroupBox", vbNullString)
If firstChildWindow <> 0 Then Exit Do
Loop

'Find the handle of the subsequent child window (it is the text box for filling the password).
If firstChildWindow <> 0 Then
timeCount = Now()
Do Until Now() > timeCount + TimeValue("00:00:05")
secondChildFirstWindow = 0
DoEvents
secondChildFirstWindow = FindWindowEx(firstChildWindow, ByVal 0&, "RICHEDIT50W", vbNullString)
If secondChildFirstWindow <> 0 Then Exit Do
Loop

'The handle was found, so...
If secondChildFirstWindow <> 0 Then
'Fill the password in the text box.
SendMessage secondChildFirstWindow, WM_SETTEXT, 0&, ByVal password

'Press the OK button (it is the default action, so no need to find the handle of the button).
PostMessage secondChildFirstWindow, WM_KEYDOWN, VK_RETURN, 0
End If
End If
End If
End Sub

Function FileExists(FilePath As String) As Boolean
'Checks if a file exists (using the Dir function... better use File System Object, or store CurDir)
Dim oldCurDir As String

On Error Resume Next
'oldCurDir = VBA.CurDir$() ' store configuration...
If Len(FilePath) > 0 Then
If Not VBA.Dir(FilePath, vbDirectory) = vbNullString Then FileExists = True
End If
'ChDir (oldCurDir) ' restore configuration...
On Error GoTo 0
End Function

Leave a Reply

Your email address will not be published. Required fields are marked *