GetWindow, FindWindow, EnumWindow, and EgtWindowX

[hatman]

So I have a solution that works (though it isn't quite as bullet-proof as I would like). What I am interested in is an explanation why NONE of the seemingly more bullet-proof solutions work.

In controlling Excel from a VB 6.0 application, at some point, the user can display the Format Axis dialog of the Excel instance, which is done programmatically using the Dialogs().Show method. The problem comes in where I need to halt the recalculation before the dialog is displayed, and resume recalc after it is manually closed. Oh, and the user may have other instances of the Excel Application open, not under of the VB 6.0 application. To do this, i need teh HWND of the Format Axis window, then periodically monitor it unil it is closed.

My first approach was to use the HWND of the XLMAIN window to find the appropriate child window. But after 3 different methods (some of which I later read in various articles actually call EnumWindows in teh background anyway, so perhaps they aren't as unique as I thought) I determined that the Format Axis window isn't visible as a child. WHY NOT???

The FindWindow function works to find the most recently created inistance of the window, which is probably fine, since it gets called immediately after the instance I am looking for is created, making it extremely unlikely to get stuck with the wrong instance.

Option Explicit
Private Declare Function EnumChildWindows _
    Lib "user32" _
    (ByVal hWndParent As Long, _
    ByVal lpEnumFunc As Long, _
    ByVal lParam As Long) As Long
    
Public Declare Function GetClassName _
    Lib "user32" Alias "GetClassNameA" _
    (ByVal hwnd As Long, _
    ByVal lpClassName As String, _
    ByVal nMaxCount As Long) As Long
    
Public Declare Function GetWindowText _
    Lib "user32" Alias "GetWindowTextA" _
    (ByVal hwnd As Long, _
    ByVal lpString As String, _
    ByVal cch As Long) As Long
    
Public Declare Function GetWindowTextLength _
    Lib "user32" Alias "GetWindowTextLengthA" _
    (ByVal hwnd As Long) As Long
    
Public Declare Function FindWindow _
    Lib "user32" Alias "FindWindowA" _
    (ByVal lpClassName As String, _
    ByVal lpWindowName As String) As Long
    
Public Declare Function GetParent _
    Lib "user32.dll" _
    (ByVal hwnd As Long) As Long
    
Public Declare Function FindWindowX _
    Lib "user32" Alias "FindWindowExA" _
    (ByVal hWnd1 As Long, _
    ByVal hWnd2 As Long, _
    ByVal lpsz1 As Long, _
    ByVal lpsz2 As Long) As Long
    
Public Declare Function GetWindow _
    Lib "user32" _
    (ByVal hwnd As Long, _
    ByVal wCmd As Long) As Long
Private Const GW_CHILD = 5
Private Const GW_HWNDNEXT = 2
Private Const GW_HWNDPREV = 3
Dim windows() As Long
Dim windowsCount As Long
Sub Compare_Them_All()
    Sheet3.Rows.Clear
    Chart4.Activate
    
    Chart4.Axes(xlPrimary).Select
    
    Application.Dialogs(xlDialogFormatLegend).Show
    Call EnumWindow_Method
    
    Call FindWindowX_Method
    
    Call FindWindow_Method
    
    Call GetWindow_Method
End Sub
Sub EnumWindow_Method()
    Dim a() As Long
    Dim cnt As Long
    Dim sClass As String
    Dim getwindowclass As String
    Dim ControlText As String
    
    sClass = Space$(256)
    GetClassName Application.hwnd, sClass, 255
    getwindowclass = Left$(sClass, InStr(sClass, vbNullChar) - 1)
    
    ControlText = String(GetWindowTextLength(Application.hwnd) + 1, Chr$(0))
    GetWindowText Application.hwnd, ControlText, Len(ControlText)
    
    Sheet3.Range("A1").Value = Application.hwnd
    Sheet3.Range("B1").Value = getwindowclass
    Sheet3.Range("C1").Value = ControlText
    
    
    a = ChildWindows(Application.hwnd)
    
    For cnt = 1 To UBound(a, 1)
    
        sClass = Space$(256)
        GetClassName a(cnt), sClass, 255
        getwindowclass = Left$(sClass, InStr(sClass, vbNullChar) - 1)
        
        ControlText = String(GetWindowTextLength(a(cnt)) + 1, Chr$(0))
        GetWindowText a(cnt), ControlText, Len(ControlText)
        
        Sheet3.Range("A" & cnt + 1).Value = a(cnt)
        Sheet3.Range("B" & cnt + 1).Value = getwindowclass
        Sheet3.Range("C" & cnt + 1).Value = ControlText
        
    Next cnt
End Sub
Function ChildWindows(ByVal hwnd As Long) As Long()
    windowsCount = 0
    
    EnumChildWindows hwnd, AddressOf EnumWindows_CBK, 1
    
    ReDim Preserve windows(windowsCount) As Long
    
    ChildWindows = windows()
    
End Function

Private Function EnumWindows_CBK(ByVal hwnd As Long, ByVal lParam As Long) As Long
    
    If windowsCount = 0 Then
    
        ReDim windows(100) As Long
        
    ElseIf windowsCount >= UBound(windows) Then
    
        ReDim Preserve windows(windowsCount + 100) As Long
        
    End If
    
    
    windowsCount = windowsCount + 1
    
    windows(windowsCount) = hwnd
    
    EnumWindows_CBK = 1
    
End Function
Sub FindWindowX_Method()
    Dim hChild As Long
    Dim cnt As Long
    Dim sClass As String
    Dim getwindowclass As String
    Dim ControlText As String
    
    hChild = FindWindowX(Application.hwnd, 0, 0, 0)
    
    cnt = 2
    
    Do
       
        sClass = Space$(256)
        GetClassName hChild, sClass, 255
        getwindowclass = Left$(sClass, InStr(sClass, vbNullChar) - 1)
        
        ControlText = String(GetWindowTextLength(hChild) + 1, Chr$(0))
        GetWindowText hChild, ControlText, Len(ControlText)
        
        Sheet3.Range("E" & cnt).Value = hChild
        Sheet3.Range("F" & cnt).Value = getwindowclass
        Sheet3.Range("G" & cnt).Value = ControlText
        
        hChild = FindWindowX(Application.hwnd, hChild, 0, 0)
       
       cnt = cnt + 1
       
    Loop Until hChild = 0
End Sub
Sub FindWindow_Method()
    Dim DialogHwnd As Long
    Dim parent_h As Long
    Dim sClass As String
    Dim getwindowclass As String
    Dim ControlText As String
    
    'this one only gets the MOST RECENT instance.
    
    DialogHwnd = FindWindow("NUIDialog", "Format Axis")
    
    parent_h = GetParent(DialogHwnd)
    
    sClass = Space$(256)
    GetClassName DialogHwnd, sClass, 255
    getwindowclass = Left$(sClass, InStr(sClass, vbNullChar) - 1)
    
    ControlText = String(GetWindowTextLength(DialogHwnd) + 1, Chr$(0))
    GetWindowText DialogHwnd, ControlText, Len(ControlText)
    
    Sheet3.Range("M2").Value = DialogHwnd
    Sheet3.Range("N2").Value = getwindowclass
    Sheet3.Range("O2").Value = ControlText
    
    sClass = Space$(256)
    GetClassName parent_h, sClass, 255
    getwindowclass = Left$(sClass, InStr(sClass, vbNullChar) - 1)
    
    ControlText = String(GetWindowTextLength(parent_h) + 1, Chr$(0))
    GetWindowText parent_h, ControlText, Len(ControlText)
    
    Sheet3.Range("M1").Value = parent_h
    Sheet3.Range("N1").Value = getwindowclass
    Sheet3.Range("O1").Value = ControlText
End Sub
Sub GetWindow_Method()
    Dim lngSrch As Long
    Dim ControlText As String
    Dim sClass As String
    Dim getwindowclass As String
    Dim cnt As Integer
    
    lngSrch = Application.hwnd
    
    lngSrch = GetWindow(lngSrch, GW_CHILD)
    
    cnt = 2
    
    Do
        lngSrch = GetWindow(lngSrch, GW_HWNDNEXT)
        
        If lngSrch = 0 Then
        
            Exit Do
            
        End If
        
        ControlText = String(GetWindowTextLength(lngSrch) + 1, Chr$(0))
        GetWindowText lngSrch, ControlText, Len(ControlText)
        
        sClass = Space$(256)
        GetClassName lngSrch, sClass, 255
        getwindowclass = Left$(sClass, InStr(sClass, vbNullChar) - 1)
        
        Sheet3.Range("I" & cnt).Value = lngSrch
        Sheet3.Range("J" & cnt).Value = getwindowclass
        Sheet3.Range("K" & cnt).Value = ControlText
        
        cnt = cnt + 1
        
    Loop
End Sub

 

[hatman]

Hi hatman.

I don't have Excel 2007 so I couldn't test the code. However I would take a different approach specially if there is a scenario where different instances could be open simultaniously.

I would install a CBT hook immediately before calling the Format Axis Dialogue window and watch for the HCBT_DESTROYWND hook ID which indicates that the Axis Format window has been closed.

This approach avoids the need to loop through endless windows plus will always return the desired instance.

Something like this :

code deleted for brevity

Note that the hook is immediatly removed upon closing the Format Axis Dialogue so it won't have a bad impact on performance or code stability.

Interesting. Without testing it, my first impression is that this is a very efficient approach, with several key advantages over my previous plan. I'm wondering how it will play out with the larger project it is going to be placed into.

Correct me if I am wrong: It looks like when the window close event gets raised, the CBTProc function gets called. How will that interact with other code that is running simultaneously?

 

[Jaafar Tribak]

Interesting. Without testing it, my first impression is that this is a very efficient approach, with several key advantages over my previous plan. I'm wondering how it will play out with the larger project it is going to be placed into.

Correct me if I am wrong: It looks like when the window close event gets raised, the CBTProc function gets called. How will that interact with other code that is running simultaneously?

the CBTProc function is called right before displaying the window and remains running until the window is closed . Once the window is closed the close event gets raised .

Having other code running simultaneously while the CBT hook is installed shouldn't be a problem. But you should test it nonetheless.

 

[hatman]

I finally got time to implement and test this (man but it's been a busy month, and it's not gonna let up any time soon).

Jaafar, this is a sweet little method when run under the Excel application. I'm still not sure I completely understand it all, but it looks to be EXACTLY what I'm looking for. It seems stable, effective and all o fteh other superlatives you want to insert here.

One minor problem:

I can't get it to do it's thing in VB 6.0. I'm wondering if if the thread ID's aren't getting crossed up somehow. Perhaps the dwThreadId when calling SetWindowsHookEx should not be the current thread, but instead one of teh trheads from teh XL application? One of teh the things that mystifies me is how you are identifying the thread for the AxisDialog... I assume that you are somehow capturing it on the fly, but it seems implicit rather than explicit.

In debug mode, I can see that the CBTProc gets called/raised iteratively until the axis dialog is closed... at least when I run this through the Excel VBE. When I move all of this code into my VB 6.0 application, which is automating Excel, I can't see that CBTProc gets called/raised at all.

If you get a chance to look at this, I'd appreciate it. Thanx Jaafar!

 

[Jaafar Tribak]

hatman.

AFAIK, you can't hook a foreign process without loading a dll into the foreign process memory space which is not straightforward.

However since you are automating Excel from VB6 , then presumably you have a pointer to the workbook where the Format Axis dialog is being displayed in which case you could conviniently run the hooking Macro remotely from VB6 via the the Application Run Method.

So based on the code I posted, if you leave the whole code inside the target workbook ( instead of placing the code in VB6 ) and remotely call the Test routine from the VB6 application something along the lines of :

Dim wb As Object
Set wb = GetObject("C:\Target.xls") 'change target path as required.
Call wb.Application.Run(wb.Name & "!Test")

In this fashion, the hook code will run inside the Excel process memory space and should then work as is.

 

[hatman]

Okay. That makes sense. If I were automating XL with an existing workbook, that approach would make a lot of sense. But what I am really doing is starting a new XL Application instance, and creating a new blank workbook from scratch, to be the repository for live streaming telemetry data from the Space Station. Then, when the user is done recording the data, the instance of XL gets set free with all of the data available for viewing and manipulating in it's own file.

I made my other approach work. Since the data refreshes once per second, the code only pings the FromatAxis window once per second after it is opened, then when it is no longer detected, the code goes dormant again.

Thanx for your input, Jafaar. As always, your response certainly gave me a lot to chew on and forced me to push my limits on API calls and the nitty gritty workings of how windows handles this stuff on a lower level. But I'm afraid I'm just not going to be able to make this approach work within this application. But I will certainly be keeping it in mind for some other projects I have brewing in the back of my head right now.

 

[Jaafar Tribak]

Hi hatman.

If you are starting a new XL Application instance and creating the workbook from scratch then you could easily export the whole hook code before hand and save it somewhere to disk.

Then you could just import the bas file into the newly created workbook on the fly and run the code like this :

Dim oApp As Object
Dim oWB As Object
 
Sub Macro()
 
    Set oApp = CreateObject("Excel.Application")
    
    Set oWB = oApp.Workbooks.Add
    
    oWB.SaveAs "C:\Target.xls"
    
    oWB.VBProject.VBComponents.Import "C:\Module1.bas"
    
    Call oWB.Application.Run(oWB.Name & "!Test")
    
    oWB.Close True
    
    ' Kill "C:\Module1.bas"    '\\ Optionally delete the bas file.
    
    oApp.Quit
 
End Sub

You will need to have the Macro security "Trust Access to the VBProject Access" set. - And even temporarly changing this security setting could be easily done via a little code.

If this doesn't work for you then the only thing I can think of is ,as I said before, have the hook code in a ActiveX dll and load the dll into the excel application process space. I haven't done this before but i'll see if I can give it a shot.

 

[hatman]

Thanx Jaafar. I didn;t expect you to put quite that much thought into it. I'll try it out and see how I like the final form.