通过UDF获取Google搜索结果 [英] Get Google search first result via UDF

问题描述

Santosh在以下链接，它在Google中搜索文本并捕获第一个URL结果为excel，无论如何，这可以用作/转换为UDF，从而公式可以是 = Googlefirsturl（A1）与 A1 具有需要搜索的文本。

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.

编辑：列2数据（名称）可以忽略

the column 2 data (Name) can be ignored

推荐答案

真的需要它作为一个功能然后

if you really need it as a function then

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

注意：您不能使用UDF修改其他单元格，因此您只能返回有一件事情进入您用来输入公式的单元格 - 我选择了您命名为

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

这篇关于通过UDF获取Google搜索结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！