CODEWITHSUNDEEP
vbaweb-scraping
Graham Dredt
Graham Dredt

Reputation: 33

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

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.

Upvotes: 1

Views: 489

Answers (1)

K.Dᴀᴠɪs
K.Dᴀᴠɪs

Reputation: 10139

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

Upvotes: 1

Related Questions