Get the titles of all open windows using VBA

Question

I have found a previous thread that shows how to get all open windows and their names using C# : get the titles of all open windows

However, I need to do this in VBA: does anyone know if that's possible?

Thank you so much.

Best regards

Answer 1

Here's a scrape from this microsoft answer by Andreas Killer that uses EnumWindows() which did the trick for me:

Option Explicit

#If Win64 Then
    Private Declare PtrSafe Function GetWindowTextLengthA Lib "user32" ( _
                                ByVal hWnd As LongPtr) As Long
    Private Declare PtrSafe Function GetWindowText Lib "user32" Alias "GetWindowTextA" ( _
                                ByVal hWnd As LongPtr, ByVal lpString As String, ByVal cch As Long) As Long
    Private Declare PtrSafe Function GetWindowLong Lib "user32" Alias "GetWindowLongA" ( _
                                ByVal hWnd As LongPtr, ByVal nIndex As Long) As Long
    Private Declare PtrSafe Function EnumWindows Lib "user32" ( _
                                ByVal lpEnumFunc As LongPtr, ByVal lParam As LongPtr) As Long
#Else
    Private Declare Function GetWindowTextLengthA Lib "user32" ( _
                                ByVal hWnd 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 GetWindowLong Lib "user32" Alias "GetWindowLongA" ( _
                                ByVal hWnd As Long, ByVal nIndex As Long) As Long
    Private Declare Function EnumWindows Lib "user32" ( _
                                ByVal lpEnumFunc As Long, ByVal lParam As Long) As Long
#End If

Private Sub debug_ListWindows()
    ListWindows
End Sub

Private Function ListWindows()
    EnumWindows AddressOf EnumFindWindowLikeProc, 0
End Function

#If Win64 Then
    Private Function EnumFindWindowLikeProc(ByVal hWnd As LongPtr, ByVal lParam As Long) As Long
#Else
    Private Function EnumFindWindowLikeProc(ByVal hWnd As Long, ByVal lParam As Long) As Long
#End If
        If IsWindowVisible(hWnd) Then
            If Len(Trim(WindowTitle(hWnd))) > 0 Then
                Debug.Print WindowTitle(hWnd)
            End If
        End If
        
        EnumFindWindowLikeProc = 1
    End Function

#If Win64 Then
    Private Function IsWindowVisible(ByVal hWnd As LongPtr) As Boolean
#Else
    Private Function IsWindowVisible(ByVal hWnd As Long) As Boolean
#End If
        Const GWL_STYLE = -16           'Sets a new window style
        Const WS_VISIBLE = &H10000000   'The window is initially visible
        
        Dim lngStyle As Long
        lngStyle = GetWindowLong(hWnd, GWL_STYLE)
        
        IsWindowVisible = ((lngStyle And WS_VISIBLE) = WS_VISIBLE)
    End Function

#If Win64 Then
    Property Get WindowTitle(ByVal hWnd As LongPtr) As String
#Else
    Property Get WindowTitle(ByVal hWnd As Long) As String
#End If
        Dim Contents As String, i As Long
        Contents = Space$(GetWindowTextLengthA(hWnd) + 1)
        
        i = GetWindowText(hWnd, Contents, Len(Contents))
        
        WindowTitle = Left$(Contents, i)
    End Property

and here is a pretty truncated version that loops using GetWindow() which should work on sufficiently new office installations:

Option Explicit

Private Declare PtrSafe Function FindWindow Lib "user32" Alias "FindWindowA" ( _
                            ByVal lpClassName As String, ByVal lpWindowName As String) As LongPtr
                            
Private Declare PtrSafe Function GetWindowTextLengthA Lib "user32" ( _
                            ByVal hWnd 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 Function GetWindowLong Lib "user32" Alias "GetWindowLongA" ( _
                            ByVal hWnd As LongPtr, ByVal nIndex As Long) As Long
                            
Private Declare PtrSafe Function GetWindow Lib "user32" ( _
                            ByVal hWnd As LongPtr, ByVal wCmd As Long) As LongPtr

Private Sub debug_ListWindows()
    ListWindows
End Sub

Private Function ListWindows()

    Const GWL_STYLE = -16           'Sets a new window style
    Const WS_VISIBLE = &H10000000   'The window is initially visible
    Const GW_HWNDNEXT = 2           'The retrieved handle identifies the window below the specified window in the Z order
    
    Dim hWnd As LongPtr
    hWnd = FindWindow(vbNullString, vbNullString)
    
    While hWnd
        Dim sTitle As String
        sTitle = Space$(GetWindowTextLengthA(hWnd) + 1)
        sTitle = Left$(sTitle, GetWindowText(hWnd, sTitle, Len(sTitle)))
        
        If (GetWindowLong(hWnd, GWL_STYLE) And WS_VISIBLE) = WS_VISIBLE Then    'only list visible windows
            If Len(Trim(sTitle)) > 0 Then                                       'ignore blank window titles
                Debug.Print sTitle
            End If
        End If
        
        hWnd = GetWindow(hWnd, GW_HWNDNEXT)
    Wend
    
End Function

Answer 2

Sub ListWindows()
    Dim wn As Excel.Window
    For Each wn In Application.Windows
        Debug.Print wn.Caption
    Next wn
End Sub

if you wanted to activate any of them simply use:

 wn.Activate