VBA Run-time Error '91' Object-variable or With block variable not set

Question

I have about 12,000 rows of data that I need websites for. This VBA code was working fine for about 800 and then stopped. Now I can't get it to run again due to this error. I can't figure out how to get it running again.

Run-time Error '91' Object-variable or With block variable not set

Sub XMLHTTP()

    Dim url As String, lastRow As Long
    Dim XMLHTTP As Object, html As Object, objResultDiv As Object, objH3 As Object, link As Object
    Dim start_time As Date
    Dim end_time As Date

    lastRow = Range("A" & Rows.Count).End(xlUp).Row

    Dim cookie As String
    Dim result_cookie As String

    start_time = Time
    Debug.Print "start_time:" & start_time

    For i = 2 To lastRow

        url = "https://www.google.co.in/search?q=" & Cells(i, 1) & "&rnd=" & WorksheetFunction.RandBetween(1, 10000)

        Set XMLHTTP = CreateObject("MSXML2.serverXMLHTTP")
        XMLHTTP.Open "GET", url, False
        XMLHTTP.setRequestHeader "Content-Type", "text/xml"
        XMLHTTP.setRequestHeader "User-Agent", "Mozilla/5.0 (Windows NT 6.1; rv:25.0) Gecko/20100101 Firefox/25.0"
        XMLHTTP.send

            Set html = CreateObject("htmlfile")
        html.body.innerHTML = XMLHTTP.ResponseText
        Set objResultDiv = html.getelementbyid("rso")
        Set objH3 = objResultDiv.getelementsbytagname("H3")(0)
        Set link = objH3.getelementsbytagname("a")(0)


        str_text = Replace(link.innerHTML, "<EM>", "")
        str_text = Replace(str_text, "</EM>", "")

        Cells(i, 2) = str_text
        Cells(i, 3) = link.href
        DoEvents
    Next

    end_time = Time
    Debug.Print "end_time:" & end_time

    Debug.Print "done" & "Time taken : " & DateDiff("n", start_time, end_time)
    MsgBox "done" & "Time taken : " & DateDiff("n", start_time, end_time)
End Sub

url = "https://www.google.co.in/search?q=" & Cells(i, 1) & "&rnd=" & WorksheetFunction.RandBetween(1, 10000) are you cracking sites? — findwindow
– findwindow, Commented Apr 27, 2016 at 22:08
I'm not sure what you mean by "cracking sites" I should mention that I found this code from this page: stackoverflow.com/questions/17495644/… — Brayheart
– Brayheart, Commented Apr 27, 2016 at 22:13
You're hitting google for random pages (RandBetween) 12k times. That's something crackers do. Just wanted to make sure. — findwindow
– findwindow, Commented Apr 27, 2016 at 22:15
Stick On Error GoTo ErrHandler at the top, then Exit Sub at the bottom, and below that make an ErrHandler: line label, put Stop and Resume instructions there, and step through your code. Where does it blow up? — Mathieu Guindon
– Mathieu Guindon, Commented Apr 27, 2016 at 22:23
Are you aware that Google has software that recognizes too many robotic searches and blocks the IP for a short period? — user4039065
– user4039065, Commented Apr 27, 2016 at 23:42

Siddharth Rout · Accepted Answer · 2016-04-28 06:28:57Z

Run-time Error '91' Object-variable or With block variable not set

You are getting that error because one of the below SET statement is failing.

Set objResultDiv = html.getelementbyid("rso")
Set objH3 = objResultDiv.getelementsbytagname("H3")(0)
Set link = objH3.getelementsbytagname("a")(0)

You need to properly handle the objects. For example

Set objResultDiv = HTML.getelementbyid("rso")

If objResultDiv Is Nothing Then
    MsgBox "Id `rso` not found for " & Cells(i, 1)
Else
    Set objH3 = objResultDiv.getelementsbytagname("H3")(0)

    If objH3 Is Nothing Then
        MsgBox "Tag `H3` not found for " & Cells(i, 1)
    Else
        Set link = objH3.getelementsbytagname("a")(0)

        If link Is Nothing Then
            MsgBox "Tag `a` not found for " & Cells(i, 1)
        Else
            '
            '~~> Rest of your code
            '
        End If
    End If
End If

Collectives™ on Stack Overflow

VBA Run-time Error '91' Object-variable or With block variable not set

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related