VBA code to select from an HTML drop down, using value rather than index

21,045

Change

op.Focus
op.selectedIndex = 2 

to

op.Value = period 

Answer by @Jordan moved to community wiki from comments

Share:
21,045
Aaron
Author by

Aaron

Updated on January 19, 2022

Comments

  • Aaron
    Aaron over 2 years

    I'm fairly new to VBA and HTML. I am writing a VBA macro which navigates through a website, before filling in a web form.

    During navigation, the script needs to pick an option from a drop-down menu. I am currently using the following (no doubt awful, but functional) code, which finds the correct drop-down menu before selecting the option at index 2.

    Set dropOptions = HTMLDoc.getElementsByTagName("select")
    For Each op In dropOptions
    If op.getAttribute("name") = "tilastojakso" Then
    op.Focus
    op.selectedIndex = 2
    Exit For
    End If
    Next op

    And here is the HTML of the relevant dropdown:

    <tr>
        <td>Statistical period</td>
        <td colspan="2"><select name="tilastojakso" >
    <option value="2016-07">2016-07 (deadline 12.08.2016)</option>
    <option value="2016-06">2016-06 (deadline 14.07.2016)</option>
    <option value="2016-05">2016-05 (deadline 14.06.2016)</option>
    <option value="2016-04">2016-04 (deadline 16.05.2016)</option>
    <option value="2016-03">2016-03 (deadline 14.04.2016)</option>
    <option value="2016-02">2016-02 (deadline 14.03.2016)</option>
    <option value="2016-01">2016-01 (deadline 12.02.2016)</option>
    <option value="2015-12">2015-12 (deadline 18.01.2016)</option>
    <option value="2015-11">2015-11 (deadline 14.12.2015)</option>
    <option value="2015-10">2015-10 (deadline 13.11.2015)</option>
    <option value="2015-09">2015-09 (deadline 14.10.2015)</option>
    <option value="2015-08">2015-08 (deadline 14.09.2015)</option>
    <option value="2015-07">2015-07 (deadline 14.08.2015)</option>
    <option value="2015-06">2015-06 (deadline 14.07.2015)</option>
    <option value="2015-05">2015-05 (deadline 12.06.2015)</option>
    <option value="2015-04">2015-04 (deadline 18.05.2015)</option>
    <option value="2015-03">2015-03 (deadline 16.04.2015)</option>
    </select></td>

    The problem:

    I want to select from the dropdown based on option value, rather than index. Let's say I have a variable called "period" which contains the string "2016-04". I want the script to pick the option with a value that matches the string stored in "period". I haven't been able to find an answer that I can understand.