Using VBA in Excel to Google Search in IE and return the hyperlink of the first result

by

As its 60,000 records i recommend use xmlHTTP object instead of using IE.
HTTP requests a easier, and a lot faster

Download the sample file

Sub XMLHTTP()

    Dim url As String, lastRow As Long, i 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")


        For Each link In objH3

            If link.className = "r" Then

                Cells(i, 2) = link.innerText
                Cells(i, 3) = link.getelementsbytagname("a")(0).href
                DoEvents
            End If
        Next
    Next

    end_time = Time
    Debug.Print "end_time:" & end_time

    Debug.Print "done" & "Time taken : " & DateDiff("n", start_time, end_time) & " :minutes"
    MsgBox "done" & "Time taken : " & DateDiff("n", start_time, end_time)
End Sub

Using CSS3 Selector

 Sub XMLHTTP1()

        Dim url As String, i As Long, lastRow As Long
        Dim XMLHTTP As Object, html As New HTMLDocument, objResultDiv As HTMLAnchorElement


        lastRow = Range("A" & Rows.Count).End(xlUp).Row
        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 = New HTMLDocument
            html.body.innerHTML = XMLHTTP.ResponseText
            Set objResultDiv = html.querySelector("div#rso h3.r a")

            Cells(i, 2) = objResultDiv.innerText
            Cells(i, 3) = objResultDiv.href

            DoEvents
        Next

    End Sub

Output

enter image description here

HTH
Santosh

More Related Contents:

  1. Sequence of numbers based on start and end value of 2 cells [closed]
  2. Function to convert column number to letter?
  3. Better way to find last used row
  4. How to insert a picture into Excel at a specified cell position with VBA
  5. 2 Dimensional array from range [closed]
  6. Save multiple sheets to .pdf
  7. Having multiple Excel instances launched, how can I get the Application Object for all of them?
  8. filter out multiple criteria using excel vba
  9. How can I create a calendar input in VBA Excel?
  10. Change date format using substitute or replace
  11. Parsing an ISO8601 date/time (including TimeZone) in Excel
  12. VBA: Convert Text to Number
  13. Concatenate cell data into another data if values matches
  14. Iterating through populated rows
  15. Using VBA to detect which decimal sign the computer is using
  16. Referencing Excel Userform Control Name Value From String (VBA)
  17. Loop through each row of a range in Excel
  18. Fill formula down till last row in column
  19. Outputting Excel rows to a series of text files
  20. How to create a range from two ranges in VBA?
  21. XML parse VBA excel (function trip, & MSXML2.DOMDocument)
  22. If string contains word from list, return value adjacent to list value
  23. Refreshing all the pivot tables in my excel workbook with a macro
  24. Countif With Multiple OR Criteria
  25. Vlookup to copy color of a cell
  26. excel vba freeze pane without select
  27. Converting a string representation of a constant into a constant?
  28. VBA WinHTTP to download file from password proteced https website
  29. How to protect Excel workbook using VBA?
  30. How to get the range of the visible rows after applying an advanced filter in Excel (VBA)

Leave a Comment