excel能翻译英语吗
230
2024-01-07
在某些情况下,您可能需要在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 Sub3. 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?Ready to supercharge your Excel tasks? Harness the power of Kutools for Excel - your ultimate time-saving tool. Streamline intricate tasks and glide through your data like a pro. Experience Excel at lightning speed!
Why You Need Kutools for Excel
🛠️ Over 300 Powerful Features: Kutools is packed with more than 300 advanced features, simplifying your work in over 1500 scenarios.
📈 Superior Data Processing: Merge cells, remove duplicates, and perform advanced data conversions – all without breaking a sweat!
⏱️ Efficient Batch Operations: Why put in extra effort when you can work smart? Import, export, combine, and tweak data in bulk with ease.
📊 Customizable Charts and Reports: Access a broad variety of additional charts and generate insightful reports that tell a story.
🗄️ Powerful Navigation Pane: Gain an advantage with the robust Column Manager, Worksheet Manager, and Custom Favorites.
📝 Seven Types of Drop-down Lists: Make data entry a breeze with drop-down lists of various features and types.
🎓 User-Friendly: A breeze for beginners and a powerful tool for experts.
Download Now and Soar Through Time with Excel!
版权声明:本文内容由网络用户投稿,版权归原作者所有,本站不拥有其著作权,亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容,请联系我们jiasou666@gmail.com 处理,核实后本网站将在24小时内删除侵权内容。