使用 html/javascript 抓取数据的 VBA 代码不起作用

Question

我想根据第一列中的输入制作VBA代码在网站上搜索。范围从A1到A102。这个代码运行良好，除了一件事：它从Excel单元格复制我的数据，然后将其粘贴到网站的搜索框中。但它不会自动点击搜索按钮。我欢迎专家们提出任何好的建议。

我知道如何从网站上抓取数据，但这个搜索框按钮有一个特定的类。我应该用什么类来点击？这个问题与VBA和javascript/html专家都有关系。

我将其作为按钮CCD_ 3得到，并且将该代码作为"类"得到；导航搜索提交文本导航精灵"；，当我单击Inspect元素时。

两者都不起作用？

感谢

Private Sub worksheet_change(ByVal target As Range)
If Not Intersect(target, Range("A1:A102")) Is Nothing Then
Call getdata
End If
End Sub
Sub getdata()
Dim i As Long
Dim URL As String
Dim IE As Object
Dim objElement As Object
Dim objCollection As Object
Set IE = CreateObject("InternetExplorer.Application")
'Set IE.Visible = True to make IE visible, or False for IE to run in the background
IE.Visible = True
URL = "https://www.amazon.co.uk"
'Navigate to URL
IE.Navigate URL
'making sure the page is done loading
Do

DoEvents
Loop Until IE.ReadyState = 4
'attempting to search date based on date value in cell
IE.Document.getElementById("twotabsearchtextbox").Value = ActiveCell.Value
'Sheets("Sheet1").Range("A1:A102").Text
'Select the date picker box and press Enter to 'activate' the new date
IE.Document.getElementById("twotabsearchtextbox").Select
'clicking the search button
IE.Document.getElementsByClassName("nav-sprite").Click
'Call nextfunction
End Sub

Answer 1

若要在Excel中使用web抓取，必须同时使用VBA和HTML。另外还有CSS和至少一些JS。最重要的是，您应该熟悉DOM(文档对象模型(。只有VBA或HTML，你不会走得太远。

对我来说，这是一个谜，为什么你想用一种复杂的方式来做，而你可以简单地通过URL来做。对于您的解决方案，您必须使用类nav-input。此类在HTML文档中存在两次。搜索按钮是第二次出现nav-input的元素。由于NodeCollection的索引从0开始，因此必须单击索引为1的元素。

Sub getdata()
Dim URL As String
Dim IE As Object
URL = "https://www.amazon.co.uk"

Set IE = CreateObject("InternetExplorer.Application")
IE.Visible = True ' True to make IE visible, or False for IE to run in the background
IE.Navigate URL 'Navigate to URL
'making sure the page is done loading
Do: DoEvents: Loop Until IE.ReadyState = 4

'attempting to search date based on date value in cell
IE.Document.getElementById("twotabsearchtextbox").Value = ActiveCell.Value

'clicking the search button
IE.Document.getElementsByClassName("nav-input")(1).Click
End Sub

编辑：使用已知ASIN公开报价的解决方案

如果你知道ASIN，你可以直接在亚马逊网页上打开报价。要使用URL中活动单元格中的ASIN(这不可靠。如果必须按Enter键才能完成输入，则活动单元格是所需单元格下的单元格(，可以将其作为参数传递给Sub()getdata():

Private Sub worksheet_change(ByVal target As Range)
If Not Intersect(target, Range("A1:A102")) Is Nothing Then
Call getdata(ActiveCell.Value)
End If
End Sub

在Sub()getdata()中，带有传输ASIN的URL被称为：

Sub getdata(searchTerm As String)
Dim URL As String
Dim IE As Object

'Use the right base url
URL = "https://www.amazon.co.uk/dp/" & searchTerm

Set IE = CreateObject("InternetExplorer.Application")
IE.Visible = True ' True to make IE visible, or False for IE to run in the background
IE.Navigate URL 'Navigate to URL
'making sure the page is done loading
Do: DoEvents: Loop Until IE.ReadyState = 4
End Sub

也可以在工作表的worksheet_change事件中完成这一切(包括获取价格和报价标题(：

Private Sub worksheet_change(ByVal target As Range)
If Not Intersect(target, Range("A1:A102")) Is Nothing Then
With CreateObject("InternetExplorer.Application")
.Visible = True ' True to make IE visible, or False for IE to run in the background
.Navigate "https://www.amazon.co.uk/dp/" & ActiveCell 'Navigate to URL
'making sure the page is done loading
Do: DoEvents: Loop Until .ReadyState = 4
'Get Price
ActiveCell.Offset(0, 1).Value = .document.getElementByID("priceblock_ourprice").innertext
'Get offer title
ActiveCell.Offset(0, 2).Value = .document.getElementByID("productTitle").innertext
End With
End If
End Sub