CODEWITHSUNDEEP
excelvbaweb-scrapinghtml-select
KingTamo
KingTamo

Reputation: 107

Select an option from drop down list excel vba scraping

How can I select an item from drop down option (the drop down is within a table structure)

Sub Test()
    Dim ie          As Object
    
    Set ie = CreateObject("InternetExplorer.Application")
    
    With ie
        .Visible = True
        .navigate ("http://www.handelsregisterbekanntmachungen.de/?aktion=suche#Ergebnis")

        Do: DoEvents: Loop Until .readyState = 4
        
        '.document.getElementsByClassName("vt").Value = "1"
    End With
End Sub

When inspecting the element I found name attribute ="vt" .. Thanks advanced for help

Upvotes: 2

Views: 1638

Answers (2)

QHarr
QHarr

Reputation: 84465

More versatile is to use CSS selectors.

Collecting all options from target element and indexing for desired option:

For example, with the day drop down which has name of vt you can use [name=vt] option

The first selector matches all option tag elements within the target parent element that has a name attribute with value of vt.

enter image description here

The selector combination is a descendant combinator that includes a leading attribute selector to target the parent drop down for day. As more than one element (all the options) are required the querySelectorAll method is used to return a nodeList which is indexed into to set a particular option to selected:

Set list = .querySelectorAll("[name=vt] option") '<==nodeList
list.item(1).Selected = True

The last line targets the day 1 option of the drop down.

Sample of CSS query results:

Selecting a single option direct

Again using the day drop down from Datum der Bekanntmachung, you can use a CSS selector of

[name=vt] option[value='2']

This is another descendant combinator combining two attribute selectors. The parent element has name attribute of vt, and the target, singular option, child element has option tag and attribute value with value of 2. This targets the day 2 option of the drop down.

VBA:

Option Explicit
Public Sub MakeSelections()
    Dim IE As New InternetExplorer
    With IE
        .Visible = True
        .navigate "http://www.handelsregisterbekanntmachungen.de/?aktion=suche#Ergebnis"

        While .Busy Or .readyState < 4: DoEvents: Wend

        Dim list As Object
        With .document

            Set list = .querySelectorAll("[name=vt] option")
            list.item(1).Selected = True         '<==select first from list
            .querySelector("[name=vt] option[value='2']").Selected = True '<Select second direct
        End With
        Stop '<== Delete me later
        .Quit
    End With
End Sub

Upvotes: 1

KingTamo
KingTamo

Reputation: 107

I could solve that by looping through the tag name "select"

    For Each e In ie.document.getElementsByTagName("select")
        'Stuff of code
    Next e

Thanks a lot

Upvotes: 0

Related Questions