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

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

Forum statistics

Threads
1,214,805
Messages
6,121,665
Members
449,045
Latest member
Marcus05

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