html - 使用重复类名的 VBA 抓取网站的值
问题描述
好的这个问题有两个部分。
- 我需要使用 VBA 将网站上的 R1200 值刮到 Excel 中。我尝试了各种方法,但我似乎无法做到正确。@QHarr 提供了一个可行的解决方案,但我认为网站的格式已经改变。
- 代码在我使用键盘按钮 F8 单步执行时运行,但在我正常运行时它会倒下。
部分:
- 这是存储值 R1200 的 HTML,“clearfix extras”元素不是唯一的,我想要的是下面显示的第 4 个:
<ul class="clearfix extras">
<li>
Document admin fee<span>R 99.00</span>
</li>
<li>
Vehicle Rental <span>R 2870.00</span>
</li>
<li>
<!--This is part of the temporary solution to show the oneway surcharge-->
One Way Drop Off Surcharge<span>R 1200.00</span>
</li>
</ul>
以下是我尝试过的所有事情,是否可以使用查询选择器来识别“clearfix extras”的第四个实例?:
`Cells(r, 3).Value = appIE.document.getElementsByClassName("thank-you-message box-shadow-dark-3").getElementsByClassName("optional-extras").querySelector(".clearfix.extras li:nth-of-type(3) span").innerText
Cells(r, 3).Value = appIE.document.getElementsByClassName("thank-you-message box-shadow-dark-3").getElementsByClassName("itinerary wide").getElementsByClassName("itinerary-container clearfix").querySelector(".optional.extras li:nth-of-type(3) span").innerText
Cells(r, 4).Value = appIE.document.querySelector(".clearfix.extras li:nth-of-type(3) span").innerText
Cells(r, 5).Value = appIE.document.getElementsByClassName("thank-you-message box-shadow-dark-3").getElementsByClassName("itinerary wide").getElementsByClassName("itinerary-container clearfix").querySelector(".clearfix.extras li:nth-of-type(3) span").innerText
Cells(r, 6).Value = appIE.document.querySelector(".thank-you-message box-shadow-dark-3.itinerary.wide.itinerary-container.clearfix.itinerary-container.clearfix li:nth-of-type(3) span").innerText
Cells(r, 7).Value = appIE.document.querySelector(".clearfix.extras:nth-of-type(3) li:nth-of-type(3) span").innerText
Cells(r, 3).Value = appIE.document.getElementsByClassName("thank-you-message box-shadow-dark-3").getElementsByClassName("itinerary wide").getElementsByClassName("itinerary-container clearfix").querySelector(".optional.extras li:nth-of-type(3) span").innerText
Set OWF = appIE.document.getElementsByClassName("thank-you-message box-shadow-dark-3")
Cells(r, 3).Value = OWF.querySelector("li:nth-of-type(2) span").innerText
Cells(r, 5).Value = appIE.document.getElementsByClassName("thank-you-message box-shadow-dark-3").getElementsByClassName("itinerary wide").getElementsByClassName("itinerary-container clearfix").querySelector(".clearfix.extras li:nth-of-type(3) span").innerText
Cells(r, 6).Value = appIE.document.querySelector(".thank-you-message box-shadow-dark-3.itinerary.wide.itinerary-container.clearfix.itinerary-container.clearfix li:nth-of-type(3) span").innerText
Cells(r, 7).Value = appIE.document.querySelector(".clearfix.extras:nth-of-type(3) li:nth-of-type(3) span").innerText`
- 我一直在使用下面的代码(我的完整代码),但我发现我的代码在不同的时间点都失败了。有一个更好的方法吗?不幸的是,我的代码必须浏览许多页面(并填写详细信息)才能达到我需要的 R1200 值。也许我的 app.wait 部分使用不正确?我知道代码不完整,我需要弄清楚如何让它循环用于所有汽车,而不仅仅是“Kia Picanto”。我只是想弄清楚如何暂时对我的问题的第 1 部分和第 2 部分进行排序。谢谢
代码:
Private Sub test1()
Dim appIE As Object
Dim e As Object
Dim ws As Worksheet
Dim wb As Workbook
Dim O
Dim a As String
Dim b As String
Dim c As String
Dim d As String
Dim PickUp As Object
Dim iL As IHTMLElement
Dim f As IHTMLElementCollection
Dim post As Object
Dim Ret As Object
Dim entry As Object
Dim l As Object
r = 2
Set wb = Application.Workbooks("Test2")
Set ws = wb.Worksheets("Sheet1")
Set appIE = CreateObject("internetexplorer.application")
With appIE
.navigate "https://www.europcar.co.za"
.Visible = True
Application.Wait (Now + TimeValue("0:00:03"))
Do While appIE.Busy
DoEvents
Application.Wait (Now + TimeValue("0:00:03"))
Loop
Application.Wait (Now + TimeValue("0:00:03"))
For i = 2 To 2
With ws
a = .Cells(i, 8)
d = .Cells(i, 9)
b = .Cells(i, 10)
c = .Cells(i, 11)
End With
Do While appIE.Busy And e Is Nothing
DoEvents
Application.Wait (Now + TimeValue("0:00:02"))
Loop
Application.Wait (Now + TimeValue("0:00:02"))
Set e = appIE.document.getElementById("PickupBranch_BranchID_id")
For Each O In e.Options
If O.Value = a Then
O.Selected = True
Exit For
End If
Next
Do While appIE.Busy And e Is Nothing
DoEvents
Application.Wait (Now + TimeValue("0:00:03"))
Loop
Application.Wait (Now + TimeValue("0:00:02"))
Set e = appIE.document.getElementById("ReturnBranch_BranchID_id")
For Each O In e.Options
If O.Value = d Then
O.Selected = True
Exit For
End If
Next
Do While appIE.Busy And f Is Nothing
DoEvents
Application.Wait (Now + TimeValue("0:00:03"))
Loop
Set f = appIE.document.getElementById("timepicker-pickup").getElementsByTagName("li")
For Each iL In f
If iL.innerText = "09" Then
iL.Click
Exit For
End If
Next iL
Do While appIE.Busy And post Is Nothing
DoEvents
Application.Wait (Now + TimeValue("0:00:02"))
Loop
Set post = appIE.document.getElementsByName("PickupDate")
For Each post In appIE.document.getElementsByName("PickupDate")
post.Value = b
Next post
Do While appIE.Busy And Ret Is Nothing
DoEvents
Application.Wait (Now + TimeValue("0:00:02"))
Loop
Set Ret = appIE.document.getElementsByName("ReturnDate")
For Each Ret In appIE.document.getElementsByName("ReturnDate")
Ret.Value = c
Next Ret
Do While appIE.Busy And l Is Nothing
DoEvents
Application.Wait (Now + TimeValue("0:00:04"))
Loop
Application.Wait (Now + TimeValue("0:00:03"))
For Each l In appIE.document.getElementsByClassName("btn search-btn")
If l.className = "btn search-btn" Then
l.Click
Exit For
End If
Next
Application.Wait (Now + TimeValue("0:00:02"))
On Error Resume Next
Do While appIE.Busy
Application.Wait (Now + TimeValue("0:00:03"))
DoEvents
Loop
Application.Wait (Now + TimeValue("0:00:02"))
For Each k In appIE.document.getElementsByClassName("filtered-vehicles")(0).getElementsByClassName("vehicle box-shadow-dark-2").getElementsByClassName("KIA PICANTO")
For Each l In appIE.document.getElementsByClassName("select-btn btn grey")
If l.className = "select-btn btn grey" Then
l.Click
Exit For
End If
Application.Wait (Now + TimeValue("0:00:02"))
Exit For
Next
Next
For Each q In appIE.document.getElementsByClassName("btn search-btn")
If q.className = "btn search-btn" Then
q.Click
Exit For
End If
Next
For Each Z In appIE.document.getElementsByClassName("btn search-btn")
If Z.className = "btn search-btn" Then
Z.Click
Exit For
End If
Next
Application.Wait (Now + TimeValue("0:00:02"))
Do While appIE.Busy
Application.Wait (Now + TimeValue("0:00:02"))
DoEvents
Loop
Application.Wait (Now + TimeValue("0:00:02"))
.document.getElementById("TitleID").Value = "8"
appIE.document.all.item("step4-initials").Value = "U"
appIE.document.all.item("step4-first-name").Value = "Underhill"
appIE.document.all.item("step4-surname").Value = "Tsonga"
appIE.document.all.item("step4-surname").Value = "Tsonga"
appIE.document.all.item("step4-email").Value = "Car@Check.com"
appIE.document.all.item("step4-contact-num").Value = "0915598452"
appIE.document.all.item("step4-id-number").Value = "0112022374"
Application.Wait (Now + TimeValue("0:00:04"))
Application.ScreenUpdating = True
On Error Resume Next
Do While appIE.Busy
Application.Wait (Now + TimeValue("0:00:03"))
DoEvents
Loop
appIE.document.getElementById("terms_and_conditions").Click
For Each Z In appIE.document.getElementsByClassName("btn search-btn")
If Z.className = "btn search-btn" Then
Z.Click
Exit For
End If
Next Z
Application.Wait (Now + TimeValue("0:00:01"))
Do While appIE.Busy
DoEvents
Application.Wait (Now + TimeValue("0:00:03"))
Loop
Cells(r, 1).Value = Mid(appIE.document.querySelector(".vehicle-information h5:nth-of-type(1) ").innerText, 7, 1)
Cells(r, 2).Value = Mid(appIE.document.querySelector(".vehicle-information h5:nth-of-type(1) ").innerText, 8, 16)
Cells(r, 3).Value = appIE.document.getElementsByClassName("thank-you-message box-shadow-dark-3").getElementsByClassName("optional-extras").querySelector(".clearfix.extras li:nth-of-type(3) span").innerText
Cells(r, 3).Value = appIE.document.getElementsByClassName("thank-you-message box-shadow-dark-3").getElementsByClassName("itinerary wide").getElementsByClassName("itinerary-container clearfix").querySelector(".optional.extras li:nth-of-type(3) span").innerText
Cells(r, 4).Value = appIE.document.querySelector(".clearfix.extras li:nth-of-type(3) span").innerText
Cells(r, 5).Value = appIE.document.getElementsByClassName("thank-you-message box-shadow-dark-3").getElementsByClassName("itinerary wide").getElementsByClassName("itinerary-container clearfix").querySelector(".clearfix.extras li:nth-of-type(3) span").innerText
Cells(r, 6).Value = appIE.document.querySelector(".thank-you-message box-shadow-dark-3.itinerary.wide.itinerary-container.clearfix.itinerary-container.clearfix li:nth-of-type(3) span").innerText
Cells(r, 7).Value = appIE.document.querySelector(".clearfix.extras:nth-of-type(3) li:nth-of-type(3) span").innerText
Cells(r, 3).Value = appIE.document.getElementsByClassName("thank-you-message box-shadow-dark-3").getElementsByClassName("itinerary wide").getElementsByClassName("itinerary-container clearfix").querySelector(".optional.extras li:nth-of-type(3) span").innerText
Set OWF = appIE.document.getElementsByClassName("thank-you-message box-shadow-dark-3")
Cells(r, 3).Value = OWF.querySelector("li:nth-of-type(2) span").innerText
Cells(r, 5).Value = appIE.document.getElementsByClassName("thank-you-message box-shadow-dark-3").getElementsByClassName("itinerary wide").getElementsByClassName("itinerary-container clearfix").querySelector(".clearfix.extras li:nth-of-type(3) span").innerText
Cells(r, 6).Value = appIE.document.querySelector(".thank-you-message box-shadow-dark-3.itinerary.wide.itinerary-container.clearfix.itinerary-container.clearfix li:nth-of-type(3) span").innerText
Cells(r, 7).Value = appIE.document.querySelector(".clearfix.extras:nth-of-type(3) li:nth-of-type(3) span").innerText
.navigate "https://www.europcar.co.za"
.Visible = True
Application.Wait (Now + TimeValue("0:00:01"))
Do While appIE.Busy
DoEvents
Application.Wait (Now + TimeValue("0:00:03"))
Loop
r = r + 1
Next i
End With
appIE.Quit
Set appIE = Nothing
End Sub
解决方案
没有测试值很难进入网站以生成您希望抓取的值。请提供这些。
您提到的该值的选择器是:
.clearfix.extras li:nth-of-type(3) span
虽然这将选择具有类名的第一个元素,clearfix extras
但您可能需要不同的索引。
第 4 个匹配类名的示例:
.clearfix.extras:nth-of-type(4) li:nth-of-type(3) span
推荐阅读
- angular - Angular 7 trackBy 的问题
- scala - 如何从“内存”表中的流式查询中访问数据以进行后续批量查询?
- javascript - Material-ui - 单击按钮时,模态打开多次
- python - 字典列表中的前 n 个键?
- r - 删除标点符号、数字和空格不起作用
- c# - 如何使用 ASP.NET Core Web API 2.2 进行分页和过滤
- vb.net - 如何使用多个文本框和一个搜索按钮进行搜索
- python - 如何从具有相同字段名的多个数据集中绘图?
- azure - 在 Azure Resource Graph 中查询资源子类型
- pyspark - 如何在 Glue ETL 中启用 pySpark?