1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User


  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How to select values from dropdown options?

Discussion in 'VBA Macros' started by shahin, Nov 14, 2017.

  1. shahin

    shahin Active Member

    Hi there!! I've written a script which is able to surf a webpage ,put certain values in two dropdown boxes, click on the search button and produce valid results. As I've never worked with dropdown selection programmatically, I don't understand the proper way to deal with it. What I've done so far is input two hardcoded values in two respective search boxes and produced the search results. How can I do the same using dropdown option and not obviously with hardcoded values?

    Here is the script I've tried so far with:

    Code (vb):

    Sub Dropdown_Items()

        Dim IE As New InternetExplorer, html As HTMLDocument
        Dim posts As Object, post As Object, elem As Object

        With IE
            .Visible = True
            .navigate "https://www.sptv.ch/trainersuche"
            Do Until .readyState = READYSTATE_COMPLETE: Loop
            Set html = .document
        End With

        Application.Wait (Now() + TimeValue("00:00:005"))

        For Each post In html.getElementsByTagName("input")
            If InStr(post.Value, "Einsatzbereiche") > 0 Then post.Value = "Golf"
        Next post

        For Each posts In html.getElementsByTagName("input")
            If InStr(posts.Value, "Region") > 0 Then posts.Value = "Bern"
        Next posts

        For Each elem In html.getElementsByTagName("input")
            If InStr(elem.Value, "Suchen") > 0 Then elem.Click
        Next elem

        Application.Wait (Now() + TimeValue("00:00:005"))

    End Sub
    Btw, the link of that webpage is already available in my script. The two dropdown boxes can be seen if you press the two images attached below. I've already marked with pencil to let you know the two search (dropdown) fields. Search input can be anything but that should be selected from the dropdown option. I've uploaded the second image only to show that how a dropdown box looks like when it is selected. Thanks in advance.

    Attached Files:

    Last edited: Nov 14, 2017
  2. Marc L

    Marc L Excel Ninja

    Hi !

    When you read the webpage code,
    you must see some option value= for each choice and
    find out the parent element as the dropdown box
    or as usual just using webbrowser inspector tool …

    Just freeze an Object variable for this Select element, stop the execution
    and foresee its properties in VBE Locals window :
    you must find out selectedIndex and Value properties …
    Or for each <option value= just a boolean Selected property
    often in case of multi selection available …

    Sometimes the onchange event must be raised
    once the Value property filled …
    shahin likes this.
  3. Chihiro

    Chihiro Excel Ninja

    As always, study the site structure before you jump to code.
    Below is the element you need to loop through to get option value (for one of the dropdown).
    Code (vb):

    <select class="upme-search-input chosen-search-filter upme-search-multiselect upme-search-meta-einsatz" name="upme_search[einsatz][]" placeholder="Einsatzbereiche" data-placeholder="Einsatzbereiche" multiple="multiple" style="display: none;">
        <option value="">Einsatzbereiche</option>
        <option value="Antara">Antara</option>
        <option value="Aquafit">Aquafit</option>
        <option value="Ausdauer">Ausdauer</option>
        <option value="Beckenbodentraining">Beckenbodentraining</option>
        <option value="Body-Combat">Body-Combat</option>
        <option value="BodyArt">BodyArt</option>
        <option value="Boxen">Boxen</option>
        <option value="Ernährungsberatung">Ernährungsberatung</option>
        <option value="Fettverbrennung">Fettverbrennung</option>
        <option value="Fitness">Fitness</option>
        <option value="Golf">Golf</option>
        <option value="Gymnastik">Gymnastik</option>
        <option value="Herz-Kreislauf Training">Herz-Kreislauf Training</option>
        <option value="Jogging">Jogging</option>
        <option value="Krafttraining">Krafttraining</option>
        <option value="Massage">Massage</option>
        <option value="Mentaltraining">Mentaltraining</option>
        <option value="Pilates">Pilates</option>
        <option value="Powerplate">Powerplate</option>
        <option value="Rehabilitation">Rehabilitation</option>
        <option value="Rückentraining">Rückentraining</option>
        <option value="Schwangerschaftsgymnastik">Schwangerschaftsgymnastik</option>
        <option value="Seniorenturnen">Seniorenturnen</option>
        <option value="Stressmanagement">Stressmanagement</option>
        <option value="Stretching">Stretching</option>
        <option value="Testing">Testing</option>
        <option value="Trainingsberatung">Trainingsberatung</option>
        <option value="(Nordic-) Walking">(Nordic-) Walking</option>
        <option value="Yoga">Yoga</option>
    shahin likes this.
  4. shahin

    shahin Active Member

    @sir chihiro, right you are. Now, I always study any particular web structures very carefully with which I work on before attempting to code. You perhaps noticed that my provided script also works but that is not my goal actually. I intended to do the same with dropdown selection. The thing is I am really helpless in this case to move on following your guideline because of lack of proper knowledge on it. Every time when I try to think like you said and Marc L also suggested, I end up doing something like i did in my provided script. This is it. Btw, I tried to follow some scripts I came across while traversing related issues in Stackoverflow but none of them worked out. Probably I was unable to follow them in the right way.
    Last edited: Nov 14, 2017
  5. Marc L

    Marc L Excel Ninja


    It seems this webpage is not usual coded for these dropdowns :
    it's the first time I see a Span object for each choice instead of a text !
    I just succeed to activate (open) a dropdown but failed to select any option
    (no much time to find out), maybe it's via some event …

    As this webpage is just a form to query data,
    better is to study the web request used, far efficient than piloting IE.

    To learn to pilot IE with dropdown, better is to use a classic webpage …
    shahin likes this.
  6. shahin

    shahin Active Member

    Thanks Marc L for your finding. This webpage is not that important for me. It seems i need to get a webpage with well-structured elements. However, as i don't know how to work with dropdown selection, I'm kinda worried if it will be difficult to deal with.
  7. Marc L

    Marc L Excel Ninja

    If that just means to read the list of a dropdown (?) in order to use it
    within a VBA procedure, you already had all the necessary !
    Just by pointing the element (a select object)
    for example by its name or its Id and read its children items (option) or
    via a collection from getElementsByTagName upon this element …

    The better initial presentation, the better & quicker solution !
    shahin likes this.

Share This Page