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

3
votes

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.

2
htmlexcelvba
Change op.Focus op.selectedIndex = 2 to op.Value = period – Jordan
Thanks Jordan, this works and is the most elegant solution! – Aaron
Another thought for speed and less code, is you might be able to get away with just selecting the name of the element, then setting it's value. e.g. HTMLDoc.getElementsByName("tilastojakso")(0).value = period. I'd check to make sure the element is an object, in case it doesn't exist on the page. – Ryan Wildry

2 Answers

1
votes

Change 

op.Focus
op.selectedIndex = 2

to

op.Value = period

Answer by @Jordan moved to community wiki from comments

0
votes

You can make use of the below code to select the options by value

Set dropOptions = HTMLDoc.getElementsByTagName("select")

For Each o In dropOptions.Options
    If o.Value = "2016-04" Then
        o.Selected = True
        Exit For
    End If
Next