1

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
Hugues Paquet Blanchette
  • 612
  • 3
  • 11
  • 24
Brayheart
  • 167
  • 2
  • 16
  • `url = "https://www.google.co.in/search?q=" & Cells(i, 1) & "&rnd=" & WorksheetFunction.RandBetween(1, 10000)` are you cracking sites? – findwindow Apr 27 '16 at 22:08
  • 1
    I'm not sure what you mean by "cracking sites" I should mention that I found this code from this page: http://stackoverflow.com/questions/17495644/using-vba-in-excel-to-google-search-in-ie-and-return-the-hyperlink-of-the-first – Brayheart Apr 27 '16 at 22:13
  • You're hitting google for random pages (RandBetween) 12k times. That's something crackers do. Just wanted to make sure. – findwindow Apr 27 '16 at 22:15
  • Does that have anything to do with the error? Its strange I can't get it to work under the original conditions when I did function properly. – Brayheart Apr 27 '16 at 22:16
  • No, I only asked because cracking is uh...frowned upon. I am not familiar with xml so can't comment. Nothing looks obviously out of line though. The loop is closed. Edit: maybe something took too long for google to respond but `doevents` should handle that? I don't know :/ Edit2: build an errorhandler if response is taking too long? – findwindow Apr 27 '16 at 22:18
  • I cut/paste your code into a fresh workbook and it runs without errors. – DeanOC Apr 27 '16 at 22:23
  • 1
    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 Apr 27 '16 at 22:23
  • Huh apparently when you google search, you append random numbers O.o I apologize for my accusation earlier. So according to that example, if 60k can be done, then 12k should be cake (suggesting one of your searches is "bad"). Check which row it errors out on and `debug.print cells(i,1).value2` – findwindow Apr 27 '16 at 22:24
  • Set objH3 = objResultDiv.getelementsbytagname("H3")(0) this is the line where it messes up apparently. Also I should note that the code doesn't work for anything anymore. It just flat out wont run even for the original 800 rows that it already worked for. – Brayheart Apr 27 '16 at 22:56
  • @DeanOC THEN WHY WONT IT WORK FOR ME?? :((. It worked for 800 rows then stopped and now wont work anymore :( – Brayheart Apr 27 '16 at 23:10
  • Can you try move `Set XMLHTTP = CreateObject("MSXML2.serverXMLHTTP")` before the For loop or put `Set XMLHTTP = Nothing` before Next? Restart Excel should make it work again? May be you need to put `XMLHTTP.waitForResponse` or check for `XMLHTTP.Status` at some stage? – PatricK Apr 27 '16 at 23:33
  • 1
    Are you aware that Google has software that recognizes too many robotic searches and blocks the IP for a short period? –  Apr 27 '16 at 23:42
  • As Jeeped said I think Google flags me after so many entries. Any Ideas on how to circumvent this? I was thinking of putting something in that would switch to internet explorer or Firefox once this happens or would that not matter? – Brayheart Apr 29 '16 at 17:15

1 Answers1

1

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
Siddharth Rout
  • 147,039
  • 17
  • 206
  • 250