Results 1 to 4 of 4

Thread: Excel VBA Runtime '91' error during lookup in Internet Explorer
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Sep 2019
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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
    Last edited by Fluff; Sep 16th, 2019 at 12:54 PM. Reason: Updated op

  2. #2
    Administrator starl's Avatar
    Join Date
    Aug 2002
    Location
    Everywhere!
    Posts
    5,859
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Excel VBA Runtime '91' error during lookup in Internet Explorer

    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...

    Code:
    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

  3. #3
    MrExcel MVP
    Join Date
    Mar 2004
    Location
    Canada
    Posts
    18,855
    Post Thanks / Like
    Mentioned
    35 Post(s)
    Tagged
    5 Thread(s)

    Default Re: Excel VBA Runtime '91' error during lookup in Internet Explorer

    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...

    Code:
    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!

  4. #4
    New Member
    Join Date
    Sep 2019
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Excel VBA Runtime '91' error during lookup in Internet Explorer

    Quote Originally Posted by Domenic View Post
    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...

    Code:
    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.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •