Excel VBA Runtime '91' error during lookup in Internet Explorer

[tomdaus]

Good day. I am trying to lookup some Warranty Expiration dates via Internet Explorer and return the results to and Excel spreadsheet. Currently the code is very rudimentary. I have not yet added to code to read serial number from the worksheet or write the results back to the spreadsheet. I just wanted to test that I could actually identify and retrieve the data from IE (First time attempting something like this). I am getting intermittent Run-time error '91' messages. One time the code will run fine, the next time it will error out. Any help would be appreciated. Thank you

Sub DellWarrantyLookup()


Dim IE As SHDocVw.InternetExplorer

Set IE = New SHDocVw.InternetExplorer

IE.Visible = True
IE.navigate "https://www.dell.com/support/home/us/en/04/product-support/servicetag/9t4hkt2"

Do While IE.readyState <> READYSTATE_COMPLETE
Application.Wait Now + TimeValue("00:00:01")
Loop

Debug.Print IE.LocationName, IE.LocationURL

Dim Doc As HTMLDocument
Set Doc = IE.document
Dim strWarranty As String

strWarranty = Doc.getElementById("warrantyExpiringLabel").innerText <-----The error is on this line

Debug.Print strWarranty
End Sub

 

[starl]

Error 91 is object not set. So, I'm guessing that the Doc object isn't getting set. Couldn't tell you why. But to get the code to run smoothly, you should verify the object is set before you try to read any values from it. So...

If Not Doc Is Nothing Then
    strWarranty = Doc.getElementById("warrantyExpiringLabel").innerText '<-----The error is on this line
End If

if your code is looping and reading multiple lines, make sure you Set Doc = Nothing before the next loop

code not tested

 

[Domenic]

It looks like the reason you're getting that error is that the page hasn't fully finished loading. If you add another Application.Wait before assigning the HTML document to the object variable Doc, the desired paragraph element should be returned.

However, a one (or two, or three) second delay may not always work. Instead, I would suggest looping until the desired element has been returned.

Also, I would suggest that you check the Busy property, in addition to the ReadyState property.

Therefore, try something like this...

Option Explicit

Sub DellWarrantyLookup()


    Dim IE As SHDocVw.InternetExplorer
    Set IE = New SHDocVw.InternetExplorer
    
    With IE
        .Visible = True
        .navigate "https://www.dell.com/support/home/us/en/04/product-support/servicetag/9t4hkt2"
        Do While .Busy Or .readyState <> READYSTATE_COMPLETE
            DoEvents
        Loop
    End With
    
    Debug.Print IE.LocationName, IE.LocationURL
    
    Dim Doc As MSHTML.HTMLDocument
    Set Doc = IE.document
    
    Const MAX_WAIT_TIME_SECS As Long = 10 'change as desired
    
    Dim startTime As Single
    startTime = Timer
    
    Dim paraElement As MSHTML.HTMLParaElement
    On Error Resume Next
    Do
        Set paraElement = Doc.getElementById("warrantyExpiringLabel")
        If Timer - startTime > MAX_WAIT_TIME_SECS Then Exit Do
    Loop While paraElement Is Nothing
    On Error GoTo 0
        
    If Not paraElement Is Nothing Then
        Dim strWarranty As String
        strWarranty = paraElement.innerText
        Debug.Print strWarranty
    Else
        MsgBox "Warranty expiry date not found!", vbExclamation
    End If
    
    Set IE = Nothing
    Set paraElement = Nothing
    
End Sub

Hope this helps!

 

[tomdaus]

It looks like the reason you're getting that error is that the page hasn't fully finished loading. If you add another Application.Wait before assigning the HTML document to the object variable Doc, the desired paragraph element should be returned.

However, a one (or two, or three) second delay may not always work. Instead, I would suggest looping until the desired element has been returned.

Also, I would suggest that you check the Busy property, in addition to the ReadyState property.

Therefore, try something like this...

Option Explicit

Sub DellWarrantyLookup()


    Dim IE As SHDocVw.InternetExplorer
    Set IE = New SHDocVw.InternetExplorer
    
    With IE
        .Visible = True
        .navigate "https://www.dell.com/support/home/us/en/04/product-support/servicetag/9t4hkt2"
        Do While .Busy Or .readyState <> READYSTATE_COMPLETE
            DoEvents
        Loop
    End With
    
    Debug.Print IE.LocationName, IE.LocationURL
    
    Dim Doc As MSHTML.HTMLDocument
    Set Doc = IE.document
    
    Const MAX_WAIT_TIME_SECS As Long = 10 'change as desired
    
    Dim startTime As Single
    startTime = Timer
    
    Dim paraElement As MSHTML.HTMLParaElement
    On Error Resume Next
    Do
        Set paraElement = Doc.getElementById("warrantyExpiringLabel")
        If Timer - startTime > MAX_WAIT_TIME_SECS Then Exit Do
    Loop While paraElement Is Nothing
    On Error GoTo 0
        
    If Not paraElement Is Nothing Then
        Dim strWarranty As String
        strWarranty = paraElement.innerText
        Debug.Print strWarranty
    Else
        MsgBox "Warranty expiry date not found!", vbExclamation
    End If
    
    Set IE = Nothing
    Set paraElement = Nothing
    
End Sub

Hope this helps!

That change makes it work perfectly every time. Thank you so much for the help with this.