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!
Bookmarks