I’ve made a couple previous post relating to bringing windows/applications to the foreground:

I was recently thinking about it, especially the case where we can’t directly retrieve the Hwnd value from the instance and I use the Title to find it and I had an idea and thought I’d share it with you all.

The Solution

I wondered if there couldn’t be a simple way to identify the Hwnd by building a list of Hwnd values prior to initiating an application and immediately after, and then comparing the two lists to identify the newly create Hwnd.

In theory, this made sense.  In practice, I found out that Microsoft application create multiple windows, not just one.  So was there a way to isolate the one I was truly after.  In the end, I believe I have to solution that works.  Thus far I have tested in Office 2013, 2019 and 2019.

To make it work I need 2 functions: 1 to generate lists of Handles, 1 to compare the 2 lists and find the newly created Hwnd.

Generate the Current List of Handles

Public Sub UIA_GetHandles(ByVal oDict As Object)
On Error GoTo Error_Handler
    Dim oUIAutomation         As UIAutomationClient.CUIAutomation
    Dim oUIADesktop           As UIAutomationClient.IUIAutomationElement
    Dim oUIAElements          As UIAutomationClient.IUIAutomationElementArray
    Dim i                     As Long

    Set oUIAutomation = New UIAutomationClient.CUIAutomation
    Set oUIADesktop = oUIAutomation.GetRootElement
    Set oUIAElements = oUIADesktop.FindAll(TreeScope_Children, oUIAutomation.CreateTrueCondition)
    For i = 0 To oUIAElements.Length - 1
        With oUIAElements.GetElement(i)
            If .CurrentClassName <> "MSO_BORDEREFFECT_WINDOW_CLASS" Then
                oDict.Add .GetCurrentPropertyValue(UIA_NativeWindowHandlePropertyId), .CurrentName
            End If
        End With
    Next i

Error_Handler_Exit:
    On Error Resume Next
    Set oUIAElements = Nothing
    Set oUIADesktop = Nothing
    Set oUIAutomation = Nothing
    Exit Sub

Error_Handler:
    MsgBox "The following error has occurred" & vbCrLf & vbCrLf & _
           "Error Source: UIA_GetHandles" & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & _
           "Error Description: " & Err.Description & _
           Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
           , vbOKOnly + vbCritical, "An Error has Occurred!"
    Resume Error_Handler_Exit
End Sub

Notice I exclude the handles with the class ‘MSO_BORDEREFFECT_WINDOW_CLASS’, these are created at the same time, but are not the handle we are after. By excluding these, we seem to be left with a single Handle.

Compare the 2 lists

#If VBA7 Then
    Public Function GetNewHandle() As LongPtr
#Else
    Public Function GetNewHandle() As Long
#End If
    On Error GoTo Error_Handler
    Dim dictkey As Variant

    Set oDictFinal = CreateObject("Scripting.Dictionary")
    Call UIA_GetHandles(oDictFinal)
    DoEvents

    'Compare the 2
    For Each dictkey In oDictFinal.Keys
        If Not oDictInitial.Exists(dictkey) Then
            GetNewHandle = dictkey
        End If
    Next dictkey

Error_Handler_Exit:
    On Error Resume Next
    Set oDictFinal = Nothing
    Set oDictInitial = Nothing
    Exit Function

Error_Handler:
    MsgBox "The following error has occurred" & vbCrLf & vbCrLf & _
           "Error Source: GetNewHandle" & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & _
           "Error Description: " & Err.Description & _
           Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
           , vbOKOnly + vbCritical, "An Error has Occurred!"
    Resume Error_Handler_Exit
End Function

Usage Example

Module level variable:

Public oDictInitial           As Object   'Scripting.Dictionary
Public oDictFinal             As Object     'Scripting.Dictionary

Actual procedure:

Public Sub LaunchExcel()
'#Const EarlyBind = True 'Use Early Binding, Req. Reference Library
    #Const EarlyBind = False    'Use Late Binding
    #If EarlyBind = True Then
        'Early Binding Declarations
        Dim oExcel            As Excel.Application
        Dim oExcelWrkBk       As Excel.WorkBook
        Dim oExcelWrSht       As Excel.WorkSheet
    #Else
        'Late Binding Declaration/Constants
        Dim oExcel            As Object
        Dim oExcelWrkBk       As Object
        Dim oExcelWrSht       As Object
    #End If
    Dim bExcelOpened          As Boolean
    Dim lRetVal               As Long
    #If VBA7 Then
        Dim lExcelHwnd        As LongPtr
    #Else
        Dim lExcelHwnd        As Long
    #End If

    Set oDictInitial = CreateObject("Scripting.Dictionary")
    Call UIA_GetHandles(oDictInitial) 'Generate our list of initial Handles

    'Start Excel
    On Error Resume Next
    Set oExcel = GetObject(, "Excel.Application")    'Bind to existing instance of Excel
    If Err.Number <> 0 Then    'Could not get instance of Excel, so create a new one
        Err.Clear
        On Error GoTo Error_Handler
        Set oExcel = CreateObject("Excel.Application")
        bExcelOpened = False
    Else    'Excel was already running
        bExcelOpened = True
    End If
    On Error GoTo Error_Handler

    oExcel.ScreenUpdating = True
    oExcel.Visible = True   'Keep Excel hidden until we are done with our manipulation
    Set oExcelWrkBk = oExcel.Workbooks.Add()    'Start a new workbook
    Set oExcelWrSht = oExcelWrkBk.Sheets(1)

    'Bring to the Front By Finding the Newly Created Handle
    lExcelHwnd = GetNewHandle() 'Identify the Newly Created Handle
    If lExcelHwnd <> 0 Then
        lRetVal = SetForegroundWindow(lExcelHwnd)
        If lRetVal = 0 Then
            'Did not get the focus for some reason
        End If
    End If

Error_Handler_Exit:
    On Error Resume Next
    Set oExcelWrSht = Nothing
    Set oExcelWrkBk = Nothing
    oExcel.ScreenUpdating = True
    Set oExcel = Nothing
    Exit Sub

Error_Handler:
    MsgBox "The following error has occurred" & vbCrLf & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & _
           "Error Source: LaunchExcel" & vbCrLf & _
           "Error Description: " & Err.Description & _
           Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
           , vbOKOnly + vbCritical, "An Error has Occurred!"
    Resume Error_Handler_Exit
End Sub

So, it is pretty straightforward at the end of the day, and now we don’t even need to get the title to perform comparisons. Just one more possible approach that can be used.