XMLHTTP Runtime error 91 when returning first URL from google search

thomassharp

Board Regular
Joined
Dec 10, 2014
Messages
84
Hi, I am getting an object variable or with block variable not set error from the line

Set objH3 = objResultDiv.getelementsbytagname("H3")(0)

I have tried solutions listed here excel - Error on getting the URL result of a Google search. - Stack Overflow
but to no avail... The code has been fine for a few weeks and I did not amend it so makes me think it is something in the sheet but everything is the same. Any help...? Thanks

Code:
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.uk/search?q=" & Cells(i, 2) & "&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, "[I]", "")
        str_text = Replace(str_text, "[/I]", "")


        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
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

Forum statistics

Threads
1,215,425
Messages
6,124,824
Members
449,190
Latest member
rscraig11

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top