如何在Excel中将Google搜索结果填充到工作表中？

如何在Excel中将Google搜索结果填充到工作表中？

在某些情况下，您可能需要在Google中进行一些重要的关键字搜索，并将最上面的搜索结果记录保留在工作表中，该工作表包含文章的标题和超链接。 本文提供了一种VBA方法，可帮助您根据单元格中给定的关键字将Google搜索结果填充到工作表中。

使用VBA代码将Google搜索结果填充到工作表中

使用VBA代码将Google搜索结果填充到工作表中

假设您需要搜索A列中列出的关键字，如下面的屏幕截图所示，请按照以下步骤用VBA代码将这些关键字的google搜索结果填充到相应的列中。

1。 按 其他 + F11 键打开 Microsoft Visual Basic应用程序 窗口。

2.在 Microsoft Visual Basic应用程序 窗口，请点击 插页 > 模块。 然后将VBA代码复制并粘贴到代码窗口中。

VBA代码：将Google搜索结果填充到工作表中

Sub xmlHttp() Updated by Extendoffice 2018/1/30 Dim xRg As Range Dim url As String Dim xRtnStr As String Dim I As Long, xLastRow As Long Dim xmlHttp As Object, xHtml As Object, xHtmlLink As Object On Error Resume Next Set xRg = Application.InputBox("Please select the keywords you will search in Google：", "KuTools for Excel", Selection.Address, , , , , 8) If xRg Is Nothing Then Exit Sub Application.ScreenUpdating = False xLastRow = xRg.Rows.Count Set xRg = xRg(1) For I = 0 To xLastRow - 1 url = "https://www.google.co.in/search?q=" & xRg.Offset(I) & "&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 xHtml = CreateObject("htmlfile") xHtml.body.innerHTML = xmlHttp.ResponseText Set xHtmlLink = xHtml.getelementbyid("rso").getelementsbytagname("H3")(0).getelementsbytagname("a")(0) xRtnStr = Replace(xHtmlLink.innerHTML, "<EM>", "") xRtnStr = Replace(xRtnStr, "</EM>", "") xRg.Offset(I, 1).Value = xRtnStr xRg.Offset(I, 2).Value = xHtmlLink.href Next Application.ScreenUpdating = True End Sub

3. Press the F5 key to run the code. In the popping up Kutools for Excel dialog box, please select the cells containing keywords you will search, and then click the OK button. See screenshot:

Then all search results including titles and links are populated into corresponding column cells based on keywords. See screenshot:

Related articles: How to populate a combo box with specified data on Workbook open? How to auto populate other cells when selecting values in Excel drop down list? How to auto populate other cells when selecting values in Excel drop down list?