如何在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?