VBA Internet Explorer Application gives different

Question

I'm trying to automate a task in excel that requires opening a webpage, navigating to a link on that page, and then clicking on a button on the second page to download an .xlsx file.

I've written a script that should do this. However, the response I get from the webpage is not always the same. In particular, sometimes this will return a download from the first page and sometimes it will navigate to the second page and not download anything, once or twice it has done both.

My sense is that this has to do with how long it takes for InternetExplorer.application to complete a request. I can't figure out how to troubleshoot this though, given that I tell the script to wait for IE.application to complete its request.

Sub DoBrowse2()

    'For Each lnk In Sheets("Sheet4").Hyperlinks
        'Range(lnk).Hy.Follow
        'Next

    Dim i As Long
    Dim URL As String
    Dim BaseURL As String
    Dim ToURL As String
    Dim IE As Object
    Dim objElement As Object
    Dim objCollection As Object
    Dim HWNDSrc As Long
    Dim html As IHTMLDocument

    Set IE = CreateObject("InternetExplorer.Application")

    URL = Range("B2").Hyperlinks(1).Address

    IE.Navigate URL

    IE.Visible = True

    Application.StatusBar = URL & " is loading. Please wait..."

    Do While IE.ReadyState = 4: DoEvents: Loop
    Do Until IE.ReadyState = 4: DoEvents: Loop

    Application.StatusBar = URL & " Loaded"

    'Set html = IE.Document
    'Dim elements As IHTMLElementCollection
    'Set elements = html.all

    For Each itm In IE.Document.all
        If itm.className = "datagrid" Then
            For Each el In itm.Document.all
                Debug.Print "hello"
                If el.className = "ujump" And Right(el.innerText, 12) = "Constituents" Then
                    'Debug.Print el.innerText
                    ToURL = el.getAttribute("data-subset")
                    BaseURL = "http://datastream.thomsonreuters.com/navigator/search.aspx?dsid=ZUCH002&AppGroup=DSAddin&host=Metadata&prev=scmTELCMBR&s=D&subset="
                    ToURL = BaseURL & ToURL
                    'Debug.Print ToURL

                    IE.Navigate ToURL
                    IE.Visible = True

                    Do While IE.Busy
                        Debug.Print "in busy loop"
                        Application.Wait DateAdd("s", 1, Now)
                    Loop

                    GoTo end_of_for
                End If
            Next
        End If
    Next

end_of_for:

    Debug.Print ("STOP STOP STOP STOP STOP")

    Dim Script As String

    For Each itm In IE.Document.all
        If itm.className = "lgc excel" Then
            Debug.Print "hello world"
            Debug.Print itm.getAttribute("onclick")
            itm.Click

            Do While IE.Busy
                Debug.Print "app busy"
                Application.Wait DateAdd("s", 1, Now)
            Loop

            Exit For

        End If
    Next

End Sub

Thanks in advance for your help.

Answer 1

Use this to determine whether IE page has been fully loaded, it always must be both of these conditions:

Do Until ie.ReadyState = 4 And ie.Busy = False
    DoEvents
Loop

Even with code above if there are scripts on the page, some content may be loaded after ie.ReadyState = 4 And ie.Busy = False condition is met and either easy way, but inefficient and unreliable Application.Wait can be used or you can try finding elements on the website which inform about loading state and determine the state by their visible attributes etc.

Part of your code is wrong and causes an endless loop:

Do While IE.ReadyState = 4: DoEvents: Loop
Do Until IE.ReadyState = 4: DoEvents: Loop

It makes DoEvents fire while readystate is complete and also until it reaches complete status.

Narrow down a collection of all elements:

For Each itm In IE.Document.all

to a specific collection for better performance when possible, for example:

For Each itm In IE.Document.GetElementsByTagName("div")