CODEWITHSUNDEEP
excelvbagoogle-search
RajK7333
RajK7333

Reputation: 25

google search in macro

I need some modifications to the macro in my previous question, like the search result 1st link in column c, 2nd link in column d, 3rd link in column e, etc up to 5 or more links respectively.

Here is the macro

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

Upvotes: 0

Views: 1182

Answers (1)

David Zemens
David Zemens

Reputation: 53623

In order to put each Hyperlink in row 1, column C et Seq., modify these lines:

Cells(i, 2) = str_text
Cells(i, 3) = link.href

and change them to this one line:

Cells(1, i+2) = link.href

The statement Cells(r, c) indicates the row and column position to be assigned, so while the code initially was putting each result & link on a new line, in columns B and C (Cells(i, 2) would be column B, etc.) you just need to modify that statement to put the data where you want it.

Upvotes: 1

Related Questions