none
Excel VBA IE Automation Runtime Error '91' RRS feed

  • Question

  • Hi Everyone,

    My following IE VBA automation code produce error 91 on runtime. But the same code is fine when I debug it. Please help!

    Sub IEAutomationAmzUK()
        Dim IE As InternetExplorer
        Dim shellWins As ShellWindows
        Dim EAN As String
        Dim objInstances, objIE

          'Call ClickamzUK

    Application.CutCopyMode = False
    Application.ScreenUpdating = False

    URL = "https://www.amazon.co.uk/"

      Set objInstances = CreateObject("Shell.Application").Windows
        If objInstances.Count > 0 Then '/// make sure we have instances open.
            For Each objIE In objInstances
    'Debug.Print objIE.LocationURL
                If InStr(objIE.LocationURL, URL) > 0 Then
                    Set IE = objIE
                        Exit For
                End If
            Next
        Else
            Set IE = CreateObject("internetexplorer.application")
                IE.Visible = True
                IE.Navigate URL
      End If

        Do While IE.Busy = True Or IE.readyState <> 4: DoEvents: Loop
    '---------------------------------------------------------------------------------------------------------------------------------------------------------------
      EAN = Trim(ActiveCell.Value)
      If IsNull(EAN) Or EAN = "" Or IsEmpty(EAN) Then GoTo AbUKs_Err
      If InStr(EAN, "-") Then EAN = Replace(EAN, "-", "")

    IE.document.getElementById("twotabsearchtextbox").Value = EAN
    If IE.document.getElementsByTagName("Input")(0).Type = "submit" Then IE.document.getElementsByTagName("Input")(0).Click
        Do While IE.Busy = True Or IE.readyState <> 4: DoEvents: Loop

    IE.document.getElementsByClassName("a-link-normal s-access-detail-page s-color-twister-title-link a-text-normal")(0).Click ' Open Link
        Do While IE.Busy = True Or IE.readyState <> 4: DoEvents: Loop
    '---------------------------------------------------------------------------------------------------------------------------------------------------------------
        If bAuthor = "search results" Then
            bAuthor = IE.document.getElementsByClassName("a-link-normal contributorNameID")(0).outerText
        Else
            Set HLs = IE.document.getElementsByTagName("a")
            For Each HL In HLs
    'Debug.Print HL.innerText & "-" & HL.href
                If InStr(HL.href, "author") > 0 Then
                    bAuthor = HL.innerHTML
                    Exit For
                End If
            Next HL
        End If
    '---------------------------------------------------------------------------------------------------------------------------------------------------------------

    The following line produced runtime error '91'
    MType = IE.document.getElementsByClassName("a-size-medium a-color-secondary a-text-normal")(0).innerText
    Set HLs = IE.document.getElementsByClassName("a-size-mini a-link-normal")

    For Each HL In HLs
    'Debug.Print HL.innerText & "-" & HL.href
                If InStr(HL.href, "hrd_new") > 0 Or InStr(HL.href, "other_meta_binding_new") > 0 Then
                    sphb = HL.innerText
                    sphb = Right(sphb, Len(sphb) - InStr(sphb, "£") + 1)
                    ActiveCell.Offset(0, 1).Value = sphb
                    'Exit For
                End If
                If InStr(HL.href, "pap_new") > 0 Or InStr(HL.href, "other_meta_binding_new") > 0 Then
                    sppb = HL.innerText
                    sppb = Right(sppb, Len(sppb) - InStr(sppb, "£") + 1)
                    ActiveCell.Offset(0, 2).Value = sphb
                    Exit For
                End If
            Next HL

    If MType = "Hardcover" Then amzsp = sphb Else amzsp = sppb
    Set HLs = Nothing

    'GoTo ExitSub
    '--------------------------------------------------------------------------------------------------------------------------------------------------------------------------

             bTitle = IE.document.getElementById("productTitle").innerText
                    If Err.Number = 438 Then MsgBox "The Link Has Been Changed"


                Set HLs = IE.document.getElementsByTagName("a")
                    Set Results = IE.document.body.getElementsByTagName("li")
                        For Each itm In Results
                            If InStr(1, itm.outerHTML, "RRP:", vbTextCompare) > 0 Then
                                    bPrice = itm.innerText
                                         p = 1
                                ElseIf InStr(1, itm.outerHTML, "Publisher:", vbTextCompare) > 0 Then
                                    bPub = itm.innerText
                                ElseIf InStr(1, itm.outerHTML, "ISBN-10:", vbTextCompare) > 0 Then
                                    bISBN10 = itm.innerText
                                ElseIf InStr(1, itm.outerHTML, "ISBN-13:", vbTextCompare) > 0 Then
                                    bISBN13 = itm.innerText
                                    Exit For
                                End If
                        Next
                        On Error Resume Next
                            If p = 0 Then
                                bPrice = IE.document.getElementsByClassName("inlineBlock-display")(0).outerText
                            End If

                    bGPN = "AmzUK"
    Set HLs = Nothing
    'objInstances , objIE

    '---------------------------------------------------------------------------------------------------------------------
    'Move Data to Excel"
    offsetColNo = Range("A1").Value
    'offsetColNo = offsetColNo - ActiveCell.Column
    'ActiveCell.Value = bAuthor
    ActiveCell.Offset(0, 1).Value = bAuthor
    ActiveCell.Offset(0, 2).Value = bTitle
    'ActiveCell.Offset(0, 1).Value = bAuthor
    ActiveCell.Offset(0, 3 + offsetColNo).Value = bPrice
    'ActiveCell.Offset(0, 4 + offsetColNo).Value = amzsp
    ActiveCell.Offset(0, 5 + offsetColNo).Value = bISBN10
    ActiveCell.Offset(0, 6 + offsetColNo).Value = bISBN13
    ActiveCell.Offset(0, 7 + offsetColNo).Value = bPub
    ActiveCell.Offset(0, 8 + offsetColNo).Value = bGPN


        Set IE = Nothing
        set shellWins = Nothing
        set EAN As String
        set objInstances = Nothing
    set objIE = Nothing

    End sub

    -------------------------------------------------------------------------

    Best Wishs,

    Kim

                                                                 
    Monday, June 25, 2018 10:38 AM

All replies

  • Hello KazimJee,

    This forum(Excel for Developers) is for discussing development issue related to Excel Object Model and I think your issue is more related to VBA. So I would move the thread to Visual Basic for Applications (VBA) forum.

    Thanks for understanding,

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, June 26, 2018 2:20 AM