Get Excel to wait for IE to load - Intermittent 424 Object Required Error

Question

My code scrapes an internal site to our company for data tables. It will hit the website many times depending on how many accounts the user needs to return. We intermittently get an

424 Object Required

in the function that gets the data from the website. I think when the load on our intranet is higher, it slows the system which may lead to this error.

I want to ensure the webpage loads before trying to obtain info from the data table. This is where my code is today:

' IE Version 8+
Set objIE = GetObject("new:{D5E8041D-920F-45e9-B8FB-B1DEB82C6E5E}") 
DoEvents

With objIE
    .Visible = False
    .Navigate "internalsite.aspx"        

    While .Busy = True Or .Readystate <> 4: DoEvents: Wend
    While .Document.Readystate <> "complete": DoEvents: Wend

    .Document.getElementById("Region_txtAccount").Value = sAccountNum

    While .Busy = True Or .Readystate <> 4: DoEvents: Wend
    While .Document.Readystate <> "complete": DoEvents: Wend

    .Document.getElementById("Region_bRunInfo").Click

    While .Busy = True Or .Readystate <> 4: DoEvents: Wend
    While .Document.Readystate <> "complete": DoEvents: Wend

End With

thisCol = 53
thisColCustInfo = 53

GetOneTable objIE.Document, 9, thisCol

'Cleanup:
    objIE.Quit
    Set objIE = Nothing

GetWebTable_Error:
    Select Case Err.Number
        Case 0
        Case Else
        Debug.Print Err.Number, Err.Description
        Stop
    End Select

I believe this thread holds the solution. I just need a little insight in applying it to my code.

Possible duplicate of [Failproof Wait for IE to load](https://stackoverflow.com/questions/23299134/failproof-wait-for-ie-to-load) — braX, Jan 25 '18 at 15:20

K.Dᴀᴠɪs · Accepted Answer · 2018-01-26T03:50:00.953

1

The unfortunate issue with Internet Explorer's .Busy and .readyState is that it is not 100% reliable with some web pages.

So a hack you can use to fix your issue is to simply wait for your object to become available. Your object would be the one that is raising the error.

Sub ieBusy(ie As Object)

    Do While ie.busy Or ie.readystate < 4
        DoEvents
    Loop

End Sub

Sub Test()

    Dim objIE As Object

    Set objIE = GetObject("new:{D5E8041D-920F-45e9-B8FB-B1DEB82C6E5E}") ' IE Version 8+
    DoEvents

    With objIE
        .Visible = False
        .Navigate "internalsite.aspx"

        ieBusy objIE

        ' this is your hack:
        On Error Resume Next
        Do While .Document.getElementById("Region_txtAccount") Is Nothing
            DoEvents
        Loop
        On Error GoTo 0
        .Document.getElementById("Region_txtAccount").Value = sAccountNum

        ieBusy objIE

        ' this is your hack:
        On Error Resume Next
        Do While .Document.getElementById("Region_bRunInfo") Is Nothing
            DoEvents
        Loop
        On Error GoTo 0
        .Document.getElementById("Region_bRunInfo").Click

        ieBusy objIE

    End With

        thisCol = 53
        thisColCustInfo = 53

    GetOneTable objIE.Document, 9, thisCol

    'Cleanup:
    objIE.Quit
    Set objIE = Nothing

GetWebTable_Error:
Select Case Err.Number
    Case 0
    Case Else
    Debug.Print Err.Number, Err.Description
        Stop
End Select

End Sub

edited Jan 26 '18 at 03:50

answered Jan 25 '18 at 15:29

K.Dᴀᴠɪs

9,945
11
33
43

Thanks, I will add your code and see if this resolves and report back. – Graham Dredt Jan 26 '18 at 03:46
This seems to have resolved my issue, i'll post back if it pops up again. Thanks! – Graham Dredt Jan 26 '18 at 18:58
@k.davis I have another question about the code you added. I seem to have a strange phenomenon now wherein when the code executes it seems to just stop actively running and I can press buttons on my userform but it has not completed the full batch. If I go into vba editor, i have to press the stop button and then it stops "running". Not sure if I'm explaining this right, but it happens sporadically and i was wondering if it had anything to do with the "on error resume next" statement? – Graham Dredt Jan 28 '18 at 08:01
@GrahamDredt the on Error resume next statement is only for that loop. Once the loop is done it says on Error Goto 0 which cancels out the first statement – K.Dᴀᴠɪs Jan 28 '18 at 16:48
Ok, I've added some debug.print strings to see what was happening when the program appears to stop executing code. Ocassionally, while it is grabbing accounts, the code you suggested gets stuck in an eternal loop while i guess it is waiting on the the "Region_txtAccount" to not be nothing. Is there a way i can make it refresh the webpage and try again? – Graham Dredt Jan 28 '18 at 20:29

Get Excel to wait for IE to load - Intermittent 424 Object Required Error

1 Answers1