VBA: Getting HTML Data from page navigated to by click

Question

I'm trying to use VBA to navigate to a web page, fill in some information, click a button, move to the next page, and collect some of the resulting information. Here is the problem that i'm having: the code goes through the first few steps, but when it tries to parse the html of the second page, it returns the HTML from the first page. For some reason, even though the correct page is shown on the screen (with the desired information), the HTML in the WebBrowser.Document never changes with the new screen loading. I've tried forcing I.E. to wait until the page is loaded, but that doesn't seem to make any difference. The code is below:

    Sub SnowLoad(Latitude As String, Longitude As String, State As String)


Dim MyHTML_Element As IHTMLElement
Dim HTMLDoc As HTMLDocument
Dim MyURL As String
Dim SnowTag As String
Dim SnowPos As Long
SnowTag = "Load"
On Error GoTo Err_Clear
MyURL = "http://snowload.atcouncil.org/"
''open new explorer
Dim MyBrowser As New InternetExplorer
MyBrowser.Silent = True
''navigate to page
MyBrowser.navigate MyURL
MyBrowser.Visible = True
''wait until ready
Do
DoEvents
Loop Until MyBrowser.readyState = READYSTATE_COMPLETE

Set HTMLDoc = MyBrowser.document
HTMLDoc.all.optionCoordinate_LATLON.Click

HTMLDoc.all.coordinate_lat.Value = Latitude
HTMLDoc.all.coordinate_lon.Value = Longitude
Set elems = HTMLDoc.getElementsByTagName("button")
    For Each e In elems

        If (e.getAttribute("class") = "btn") Then
            e.Click
            Exit For
        End If

    Next e

Do
DoEvents
Loop Until MyBrowser.readyState = READYSTATE_COMPLETE

Set elems = HTMLDoc.getElementsByTagName("p")

    For Each e In elems
        Debug.Print e.innerHTML
        If InStr(e.innerHTML, SnowTag) Then
            SnowPos = InStr(e.innerHTML, SnowTag)
            Range("SnowPosition").Value = SnowPos
            Exit For
        End If

    Next e

Err_Clear:
 If Err <> 0 Then
 Err.Clear
 Resume Next
 End If

End Sub

I for the life of me can't figure out how to get the information shown on the second page. No amount of googling seems to have produced an answer or a similar issue. Could it be related to using the button to naviate in lieu of an actual call to .navigate?

Answer 1

I've got this working seemingly reliably. I just ran it 5-6 times without an issue. I too was not finding the element after the page was refreshing. To get around this, I got a brand new reference to the object after the load. That seemed to work well.

Also, I did a general clean up on the code.

#If VBA7 Then
    Public Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As LongPtr)
#Else
    Public Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
#End If

Sub SnowLoad()
On Error GoTo errhand:
    'I hard coded these for testing
    Latitude = "46"
    Longitude = "-87"

    Dim MyURL       As String: MyURL = "http://snowload.atcouncil.org/"
    Dim element     As Object

    ''open new explorer, I used late binding
    Dim MyBrowser   As Object: Set MyBrowser = CreateObject("InternetExplorer.Application")
    MyBrowser.Visible = True
    MyBrowser.navigate MyURL

    'Wait for the browser to finish loading
    waitForLoad MyBrowser

    With MyBrowser.document
        .getElementByID("optionCoordinate_LATLON").Click
        .getElementByID("coordinate_lat").Value = Latitude
        .getElementByID("coordinate_lon").Value = Longitude
        .getElementsByName("btn-submit")(0).Click
    End With

    waitForLoad MyBrowser

    'For whatever reason the HTML isn't updating along with the page
    'Instead, I'm just getting an updated reference to the IE object with the
    'below function, weird issue, but this seems to work
    Set MyBrowser = FindWindow("component/vcpsnowload/item")
    Set element = MyBrowser.document.Forms("adminForm").getelementsByTagName("p")(0)
    Range("A1").Value = element.innertext

    Exit Sub
errhand:
    MsgBox (Err.Number & "  " & Err.Description)
End Sub

Public Function FindWindow(SearchCriteria As String) As Object
    Dim window As Object

    For Each window In CreateObject("Shell.Application").Windows
        If window.locationurl Like "*" & SearchCriteria & "*" Then
            Set FindWindow = window
            Exit Function
        End If
    Next

    Set FindWindow = Nothing
End Function

Public Sub waitForLoad(ByVal IE As Object)
    Dim i As Byte
    Sleep 500 ' wait a bit for the page to start loading
    Do
        i = i + 1
        Sleep 500
    Loop Until IE.readystate = 4 Or IE.busy = False Or i >= 20
End Sub

The code above is returning this: Any elevation: Ground Snow Load is 60 psf