ie automation run-time error

Hello all,

I am trying to run a macro to search the string in column a into Google, retrieve the URL of the first search result, and place the resulting title and url into columns c and d, respectively. I retrieved the code for this macro from some answers site, but I understand the gist of it. The macro tends to work for a little while - say 40 rows - but then returns a run-time error. After the run-time error, if I stop the macro and immediately run again, the error will re-occur until I wait a certain amount of time. The error reads: "Run-time error '-2147024891 (80070005)': Access is denied." Is there a way to beat this error?

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


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

    For i = 2 To lastRow

        URl = "https://www.google.co.in/search?q=" & Cells(i, 1)

        Set xmlHttp = CreateObject("MSXML2.XMLHTTP")
        xmlHttp.Open "GET", URl, False
        xmlHttp.setRequestHeader "Content-Type", "text/xml"
        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
    Next
End Sub

Thanks!

Thanks for your response.
Well I don't think it's a specific term/terms, because the code worked for the first 30-40 terms, then gave me a run-time error, but when I ran it again a couple hours later, the code continued on lines 40-60ish, then gave me another run-time error, and I ran it again until line 90ish. No specific search string seems to have given an error, but it seems after a certain amount of time sending the xml request, my access is denied...

It's possible that you are being blocked, automation of google searches in this way is strictly against their terms and conditions - it's possible that they therefore put a limit on the number of requests that you can make in a given time period

Hi Kyle, That does seems a plausible reason for getting denied access. However, I have made Google searches in IE immediately after getting the run-time error, with no such issue...

I suspect it's the frequency of requests, currently you are making requests as fast as vba allows, google doesn't like this, making a request then in internet explorer wil then have a different request profile and be much slower.

Ah I see. Thank you.