Read IE9 notification bar from Excel VBA

[FractalWalk]

I have written an Excel VBA macro that opens an IE9 window and goes through a series of forms and filters to create and download a report. The final step is to click an element to save the report to my local drive. At that point the report and its URL is created by Javascript on the fly so I don't know ahead of time what it will be. IE9 then provides it's file download dialog box to Open/Save/Cancel. My code then selects the "Save" option and the download begins.

My problem is that I need to know when the download completes so that I can run the next report. IE9 provides the yellow notification bar at the bottom of my browser window with a changing text showing the progress. I need to be able to know when the bar says the download has completed. I know the handle of the notification bar, but I don't know how to read the text of the window. Is there some API where I could pass the window handle and get the text? Or some way in VBA to select the window to get a status update?

 

[bubba100]

Have you looked into DoEvents.

 

[FractalWalk]

What do you mean? I know I will likely have to utilize DoEvents in my code but that isn't my question.

My question is if I know the handle of the IE notification bar is there VBA code or an API I can use to extract the text that is displayed in that bar? DoEvents doesn't do that.

 

[combo]

How do you instantiate and manage internet explorer? I don't know version 9, but in earlier versions it was possible to add browser control to userform or instantiate it standalone. using early binding. In the first case you can handle events provided by the control directly, in the latter use custom class module with WithEvents object declaration and event procedures in the class.

combo

 

[FractalWalk]

Thanks for the reply. Unfortunately I am not an experienced programmer so most of what you said went right over my head and a couple of days reading hasn't helped me understand it any better.

How do you instantiate and manage internet explorer?
Well, the original IE window is launched with this:

    Set IE = CreateObject("InternetExplorer.Application")
    IE.Navigate URL_Behind_Firewall
    IE.Visible = True
    Do While IE.readyState <> 4
        DoEvents
    Loop

But I am trying to control the download notification bar launched by IE (or maybe windows?). I can't seem to control that with my IE object or if I can I don't know how and I am asking for that info.


it was possible to add browser control to userform or instantiate it standalone. using early binding
Sorry, I have no idea what that means.


In the first case you can handle events provided by the control directly
That is my problem. I assume it is some type of form with a control (textbox) that can be read. I am asking if that window has some control or property or something that I can read to see if the text contained in it shows that the download has been completed. The only thing I know about the window is the handle. I am looking for something like:

'This part actually works.
Child_hndl = 0
Do While Child_hndl = 0
   Parent_hndl = FindWindow(vbNullString, "Internet Explorer")
   Child_hndl = FindWindowEx(Parent_hndl, ByVal 0&, vbNullString, vbNullString)
Loop

'I'm looking for this part which I know is garbage but hopefully you get the idea. 
Do until IE.Forms(Child_hndl).text like "*download complete*"
   DoEvents
Loop

'or maybe some type of API 
Do until txt like "*download complete*"
   txt = GetWindowCaption(Child_hndl, "TextBox")
   DoEvents
Loop

in the latter use custom class module with WithEvents object declaration and event procedures in the class
No clue what that means.

 

[combo]

Webbrowser control in the userform:
Try to add a webbrowser control to the toolbox: right-click the toolbox and choose "additional controls". Search for "Microsoft Web Browser", tick the control and accept, you should see a new globe icon in the toolbox. If you can't find it, add a reference to Microsoft Internet Controls (as below) and try again.
If you add the control to your userform, you get a regular IE window on the form, you can use its properties, methods and events to interact with it.

The rest below is for case of working with standalone internet explorer.
WithEvents is a kind of variable declaration, that enables you to pick and handle available events exposed by this variable, like in case of controls on the userform or userform itself.
There are some limitations however:
- WithEvents declaration has to be in a class module (or document module, that is derived from a class module),
- WithEvent cannot be declared As Object, it requires early binding and thus a reference to proper library.

Setting a reference for early binding:
in vbe: tools>references, scroll the list and search "Microsoft Internet Controls", check if found. If not, for IE8:
- find ieframe.dll,
- in vbe: tools>references>Browse..., point to ieframe,dll.
A new reference to Microft Internet Controls should be ticked. A new SHDocVw library should be visible in object browser.

Create a class with WithEvents declaration and event procedure:
- add class module and name it (can be any other) IEclass,
- add code:

Public WithEvents IE As SHDocVw.InternetExplorer

Private Sub IE_OnQuit()
MsgBox "OnQuit event"
End Sub

You can see other events available for IE in the right dropdown list.

Create internet explorer and link it to IE in instantiated class to get IE events, can be in standard or userform module. See what's going on:

Public IE As SHDocVw.InternetExplorer

Sub InstantiateIE()
Dim IEEventHandler As IEclass
Set IEEventHandler = New IEclass
Set IE = New SHDocVw.InternetExplorer
Set IEEventHandler.IE = IE
IE.Visible = True
IE.Quit
End Sub

You can also see my vba faq about events.

combo

 

[FractalWalk]

OK thanks, that's a lot of info for me to digest. But before I do let me ask this. You keep referencing a user form:

If you add the control to your userform
. . . like in case of controls on the userform or userform itself.

What userform are you talking about? I am trying to read a window that the system is displaying. It isn't a window that I created. So how can I add controls to it?

 

[combo]

Instead of creating ie window you can use userform and add webbrowser control and browse here instead. Doubleclick the webbrowser in designer and you will see default event event procedure. If you still need its hwnd, use Me.WebBrowser1.HWHD call (assuming code in userform and staying with default name of webbrowser).

combo