2

There is a fantastic answer (vba code) by Santosh on the following link which search text in Google and captures the first URL result in excel, is there by anyway this can be used/converted as UDF, whereby the formula can be =Googlefirsturl(A1) with A1 having the text which needs to be searched.

Edit: the column 2 data (Name) can be ignored

Community
  • 1
  • 1
Vasim
  • 3,052
  • 3
  • 35
  • 56

1 Answers1

4

if you really need it as a function then

enter image description here

Option Explicit

Function GoogleFirstUrl(search As String) As String

    Dim i As Long
    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

    Dim cookie As String
    Dim result_cookie As String

    DoEvents
    url = "https://www.google.co.in/search?q=" & search & "&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)

    GoogleFirstUrl = link.href

End Function

Note: you cannot modify other cells using a UDF so you can return only one thing at a time into the cell you've used to enter the formula into - I selected the URL as you named your function