VBA循环从网页下拉元素并下载到excel表 [英] VBA loop through dropdown elements from web page and download to excel sheet
本文介绍了VBA循环从网页下拉元素并下载到excel表的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我想使用 VBA 从 amazon.com 网页上的下拉元素中选择特定元素.我想在要求客户选择他们想要添加到购物车的产品数量的情况下执行此操作.如何使用 VBA 从下拉菜单中选择不同的值，例如 3 并将该值显示到 Excel 表中?现在，即使我设置了要选择的不同元素，我也总是得到默认值 1.
Sub Basics_Of_Web_Macro()将 myIE 调暗为对象将 myIEDoc 调暗为对象'启动 Internet ExplorerSet myIE = CreateObject("InternetExplorer.Application")'如果你想看到窗口设置为TruemyIE.Visible = False'现在我们打开我们想用作信息来源的页面myIE.navigate "https://www.amazon.com/belif-True-Cream-Aqua-Korean/dp/B00H4GOAZO/ref=pd_cart_crc_cko_cp_2_2/139-8277217-3794320?_encoding=UTF8&GOB_4Hamp_d8&GOB_4Hampe;GOAZO/ref=pd_cart_crc_cko_cp_2_28a11-4ab0-8173-5d0dbaff1938&安培; pd_rd_w = Hm8FW&安培; pd_rd_wg = Hpv4X&安培; pf_rd_p = eff166ab-25d2-4f2c-a51d-0a0e86061f9d&安培; pf_rd_r = EVT26E6K7CV8T1QMTY7H&安培; PSC = 1&安培; refRID = EVT26E6K7CV8T1QMTY7H"'我们等待资源管理器实际打开页面并完成加载虽然 myIE.Busy事件温德'现在让我们读取页面的 HTML 内容设置 myIEDoc = myIE.document'是时候获取我们想要的信息了设置元素 = myIEDoc.getElementsByClassName("a-dropdown-prompt")如果elements.Length >0 那么范围("A1") = 元素(2).innerText万一结束子亚马逊源代码示例如下所示，下拉菜单中有大约 30 个元素可供选择的数量:
...
<span class="a-dropdown-container"><label for="quantity" class="a-native-dropdown">Qty:</label><select name="quantity" autocomplete="off" id="quantity" tabIndex="-1" class="a-native-dropdown"><option value="1" selected>1</选项><option value="2" selected>2</选项><option value="3" selected>3</选项>...
解决方案 你通常可以使用一个 attribute = value 选择器通过它的 value 属性值来定位选择的选项
myIEDoc.querySelector("#quantity [value='3']").selected = True
您可以在
中连接该值选项显式'VBE >工具 >参考:' 微软互联网控制公共子 SelectQuantity()Dim ie 作为新的 Internet ExplorerConst QTY As Long = 3与即.可见 = 真.Navigate2 "https://www.amazon.com/belif-True-Cream-Aqua-Korean/dp/B00H4GOAZO/ref=pd_cart_crc_cko_cp_2_2/139-8277217-3794320?_encoding=UTFr8&iGO_amp_d0eH&4Bd7d0eH&4Bd0d0eH8a11-4ab0-8173-5d0dbaff1938&安培; pd_rd_w = Hm8FW&安培; pd_rd_wg = Hpv4X&安培; pf_rd_p = eff166ab-25d2-4f2c-a51d-0a0e86061f9d&安培; pf_rd_r = EVT26E6K7CV8T1QMTY7H&安培; PSC = 1&安培; refRID = EVT26E6K7CV8T1QMTY7H"虽然 .Busy 或 .readyState <4:DoEvents:温德使用 .document.querySelector("#quantity [value='" & QTY & "']").Selected = TrueActiveSheet.Range(A1") = 数量停止结束于结束于结束子
遍历所有
选项显式'VBE >工具 >参考:' 微软互联网控制公共子 SelectQuantity()Dim ie As New InternetExplorer, numberOfOptions As Long, i As Long与即.可见 = 真.Navigate2 "https://www.amazon.com/belif-True-Cream-Aqua-Korean/dp/B00H4GOAZO/ref=pd_cart_crc_cko_cp_2_2/139-8277217-3794320?_encoding=UTFr8&iGO_amp_d0eH&4Bd7d0eH&4Bd0d0eH8a11-4ab0-8173-5d0dbaff1938&安培; pd_rd_w = Hm8FW&安培; pd_rd_wg = Hpv4X&安培; pf_rd_p = eff166ab-25d2-4f2c-a51d-0a0e86061f9d&安培; pf_rd_r = EVT26E6K7CV8T1QMTY7H&安培; PSC = 1&安培; refRID = EVT26E6K7CV8T1QMTY7H"虽然 .Busy 或 .readyState <4:DoEvents:温德使用 .documentnumberOfOptions = .querySelectorAll("#quantity option").Length -1 '收集带有 id 数量的父选择元素的选项标签元素的子元素对于 i = 0 到 numberOfOptions.querySelector("#quantity [value='" & i & "']").Selected = TrueActiveSheet.Cells(i +1, 1) = i '下一个停止结束于结束于结束子
匹配更接近你的
公共子基础知识_Of_Web_Macro()将 myIE 调暗为对象Const QTY As Long = 3'启动 Internet ExplorerSet myIE = CreateObject("InternetExplorer.Application")'如果你想看到窗口设置为TruemyIE.Visible = False'现在我们打开我们想用作信息来源的页面myIE.navigate2 "https://www.amazon.com/belif-True-Cream-Aqua-Korean/dp/B00H4GOAZO/ref=pd_cart_crc_cko_cp_2_2/139-8277217-3794320?_encoding=UTF8&4_pdOrd_7rdOrd_9&4BdOrd_20-8a11-4ab0-8173-5d0dbaff1938&安培; pd_rd_w = Hm8FW&安培; pd_rd_wg = Hpv4X&安培; pf_rd_p = eff166ab-25d2-4f2c-a51d-0a0e86061f9d&安培; pf_rd_r = EVT26E6K7CV8T1QMTY7H&安培; PSC = 1&安培; refRID = EVT26E6K7CV8T1QMTY7H"'我们等待资源管理器实际打开页面并完成加载而 myIE.Busy 或 myIE.readyState <4:DoEvents:温德使用 myIE.document.querySelector("#quantity [value='" & QTY & "']").Selected = TrueActiveSheet.Range(A1") = 数量停止结束于结束子
I want to select a specific element from the drop-down elements on the amazon.com web page using VBA. I want to perform this where a customer is required to select the quantity of the product they want to add to their shopping cart. How can i choose a different value e.g., 3 from the drop down menu using VBA and display the value into an excel sheet? For now, i am always getting the default value 1 even if i set for a different element to be selected.
Sub Basics_Of_Web_Macro()
Dim myIE As Object
Dim myIEDoc As Object
'Start Internet Explorer
Set myIE = CreateObject("InternetExplorer.Application")
'if you want to see the window set this to True
myIE.Visible = False
'Now we open the page we'd like to use as a source for information
myIE.navigate "https://www.amazon.com/belif-True-Cream-Aqua-Korean/dp/B00H4GOAZO/ref=pd_cart_crc_cko_cp_2_2/139-8277217-3794320?_encoding=UTF8&pd_rd_i=B00H4GOAZO&pd_rd_r=e154e278-8a11-4ab0-8173-5d0dbaff1938&pd_rd_w=Hm8FW&pd_rd_wg=Hpv4X&pf_rd_p=eff166ab-25d2-4f2c-a51d-0a0e86061f9d&pf_rd_r=EVT26E6K7CV8T1QMTY7H&psc=1&refRID=EVT26E6K7CV8T1QMTY7H"
'We wait for the Explorer to actually open the page and finish loading
While myIE.Busy
DoEvents
Wend
'Now lets read the HTML content of the page
Set myIEDoc = myIE.document
'Time to grab the information we want
Set elements = myIEDoc.getElementsByClassName("a-dropdown-prompt")
If elements.Length > 0 Then
Range("A1") = elements(2).innerText
End If
End Sub
A sample of the amazon source code looks like this with about 30 elements in the drop-down menu for the number of quantity one can choose:
...
<div class="a-column a-span12 a-text-left">
<span class="a-dropdown-container"><label for="quantity" class="a-native-dropdown">Qty:</label><select name="quantity" autocomplete="off" id="quantity" tabIndex="-1" class="a-native-dropdown">
<option value="1" selected>1
</option>
<option value="2" selected>2
</option>
<option value="3" selected>3
</option>
...
解决方案 You can usually use an attribute = value selector to target the option of choice by its value attribute value
myIEDoc.querySelector("#quantity [value='3']").selected = True
You can concatenate that value in
Option Explicit
'VBE > Tools > References:
' Microsoft Internet Controls
Public Sub SelectQuantity()
Dim ie As New InternetExplorer
Const QTY As Long = 3
With ie
.Visible = True
.Navigate2 "https://www.amazon.com/belif-True-Cream-Aqua-Korean/dp/B00H4GOAZO/ref=pd_cart_crc_cko_cp_2_2/139-8277217-3794320?_encoding=UTF8&pd_rd_i=B00H4GOAZO&pd_rd_r=e154e278-8a11-4ab0-8173-5d0dbaff1938&pd_rd_w=Hm8FW&pd_rd_wg=Hpv4X&pf_rd_p=eff166ab-25d2-4f2c-a51d-0a0e86061f9d&pf_rd_r=EVT26E6K7CV8T1QMTY7H&psc=1&refRID=EVT26E6K7CV8T1QMTY7H"
While .Busy Or .readyState < 4: DoEvents: Wend
With .document
.querySelector("#quantity [value='" & QTY & "']").Selected = True
ActiveSheet.Range("A1") = QTY
Stop
End With
End With
End Sub
Loop over all
Option Explicit
'VBE > Tools > References:
' Microsoft Internet Controls
Public Sub SelectQuantity()
Dim ie As New InternetExplorer, numberOfOptions As Long, i As Long
With ie
.Visible = True
.Navigate2 "https://www.amazon.com/belif-True-Cream-Aqua-Korean/dp/B00H4GOAZO/ref=pd_cart_crc_cko_cp_2_2/139-8277217-3794320?_encoding=UTF8&pd_rd_i=B00H4GOAZO&pd_rd_r=e154e278-8a11-4ab0-8173-5d0dbaff1938&pd_rd_w=Hm8FW&pd_rd_wg=Hpv4X&pf_rd_p=eff166ab-25d2-4f2c-a51d-0a0e86061f9d&pf_rd_r=EVT26E6K7CV8T1QMTY7H&psc=1&refRID=EVT26E6K7CV8T1QMTY7H"
While .Busy Or .readyState < 4: DoEvents: Wend
With .document
numberOfOptions = .querySelectorAll("#quantity option").Length -1 'gather option tag element children of parent select element with id quantity
For i = 0 To numberOfOptions
.querySelector("#quantity [value='" & i & "']").Selected = True
ActiveSheet.Cells(i +1, 1) = i '
Next
Stop
End With
End With
End Sub
Matching closer to yours
Public Sub Basics_Of_Web_Macro()
Dim myIE As Object
Const QTY As Long = 3
'Start Internet Explorer
Set myIE = CreateObject("InternetExplorer.Application")
'if you want to see the window set this to True
myIE.Visible = False
'Now we open the page we'd like to use as a source for information
myIE.navigate2 "https://www.amazon.com/belif-True-Cream-Aqua-Korean/dp/B00H4GOAZO/ref=pd_cart_crc_cko_cp_2_2/139-8277217-3794320?_encoding=UTF8&pd_rd_i=B00H4GOAZO&pd_rd_r=e154e278-8a11-4ab0-8173-5d0dbaff1938&pd_rd_w=Hm8FW&pd_rd_wg=Hpv4X&pf_rd_p=eff166ab-25d2-4f2c-a51d-0a0e86061f9d&pf_rd_r=EVT26E6K7CV8T1QMTY7H&psc=1&refRID=EVT26E6K7CV8T1QMTY7H"
'We wait for the Explorer to actually open the page and finish loading
While myIE.Busy Or myIE.readyState < 4: DoEvents: Wend
With myIE.document
.querySelector("#quantity [value='" & QTY & "']").Selected = True
ActiveSheet.Range("A1") = QTY
Stop
End With
End Sub
这篇关于VBA循环从网页下拉元素并下载到excel表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
