select drop-down list by text not value

Question

I am trying to write an Excel VBA script to navigate to a website and select from a drop-down list using the text in the list item, NOT the value of the index. By examining the HTML code, I can see that the values in the drop-down list appear to be integers with no recognizable pattern and do not appear to correlate to the text shown for the entry in the drop-down list.

<option value="246">new2</option>
<option value="245">new</option>
<option value="196">test</option>

I can successfully use the following command:

ieDoc.getElementById("viewMaintainFixedCombustible_fixedCombustible_fireZoneId").Value = 246

To select the item "new2" from the drop-down list, by sending the value 246. Or, I could send 245 to select "new". However, the elements in the list are always changing, so selecting by value (246, 245, etc.) is impractical. Thus, I am searching for a way to select an entry from the drop-down list (e.g. "new2") by sending the text name of the entry (e.g. "new2") in VBA.

Does anyone have a method for how to do this?

Are you able to read (in VBA) both the text and the values for these options? If so, if nothing else works, you could always store them in an array or scripting dictionary to do a lookup. — David Zemens
Are you saying iterate through the entire drop-down and pull out all values? I'm not familiar with how to pull the text and values from the drop-down back into Excel, but this could be a viable approach. — user2344525
I'm not sure if it can be done -- I don't do very much Excel/IE automation, but if there's no clear way to get the options by name (e.g., quivalent to GetElementByName then I would try to find a way to iterate over the options. Just a suggestion, not really an "answer" per se, but figured I'd give you something to try at least :) — David Zemens

Santosh Santosh · Accepted Answer · 2013-05-03T01:15:49

Try below sample code to set dropdown list by text.

    Set drp =ieDoc.getElementById("<id of your dropdown>")
    For x = 0 To drp.Options.Length - 1
        If drp.Options(x).Text = "new2" Then
            drp.selectedIndex = x
            Exit For
        End If
    Next

select drop-down list by text not value

1 Answers