VBA loop through dropdown elements from web page and download to excel sheet

Question

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>

...

QHarr · Accepted Answer · 2021-06-10 16:01:25Z

1

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

edited Jun 10, 2021 at 16:01

answered May 6, 2019 at 4:36

QHarr

84.5k14 gold badges58 silver badges105 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

giogio Over a year ago

When i run the first code you provided, i am getting the error 'User-defined type not defined', and debugging the error highlights this line as the causing the error: Public Sub SelectQuantity()

QHarr Over a year ago

The only reference is Microsoft internet controls - I assume you have this added?

giogio Over a year ago

I am new (like 2 days old) to VBA, so can you please just maybe add the necessary line i need using my original code instead of rewriting the entire code?

QHarr Over a year ago

added at bottom version closer to your naming and layout.

giogio Over a year ago

Its producing the error "object required" on the line: .querySelector("#quantity [value='" & QTY & "']").Selected = True. Where is QTY defined in the new code?

|

Collectives™ on Stack Overflow

VBA loop through dropdown elements from web page and download to excel sheet

1 Answer 1

8 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related