Using excel vba to change the value of a dropdown

问题:

I am writing an Excel macro to fill out a form on a website. I have written the code that populate the text boxes easily enough, and found code to chose radio boxes, but I am having problems with choosing info from dropdown menus.

Example 'Gender':

The combo box has three options:

Select / Male / Female

I've tried a few variations on this:

doc.getElementsByName("xs_r_gender").Item(0).Value="Male"

...but with no luck.

This is the web source code:

<td> <select name="xs_r_gender" id="xs_r_gender">
<option value="" selected>Select</option>
<option value="male">Male</option>
<option value="female">Female</option> </select></td>

Thanks.

回答1:

doc.getElementById("xs_r_gender").selectedindex=1
seems to do the trick. (Where 1 represents male)

Though it means I will need to do alot of lookups to determine what the value is for the items in my dropdown. (Easy enough for Sex, where there are only two options, but I have some comboboxes with up to 50 options). If anyone knows of a faster solution, that'd be great. In the meantime, Ill start doing up some tables!!!

thanks.

回答2:

Try below code assuming doc = ie.document

doc.getElementById("xs_r_gender").value = "Male"

回答3:

Use this in your code to call the function below.

xOffset = SetSelect(IE.Document.all.Item("shipToStateValue"), "Texas")
doc.getElementById("shipToStateValue").selectedindex = xOffset

Then use this for your function

Function SetSelect(xComboName, xComboValue) As Integer
    'Finds an option in a combobox and selects it.

    Dim x As Integer

    For x = 0 To xComboName.options.Length - 1
        If xComboName.options(x).Text = xComboValue Then
            xComboName.selectedindex = x
            Exit For
        End If
    Next x

    SetSelect = x

End Function

回答4:

Thanks Stack, works for me! My solution to operate an IE HTML combobox drop down turned out to be two parts.

Part 1 was to click the pull down, here's code:

Dim eUOM1 As MSHTML.HTMLHtmlElement
Set eUOM1 = ie.document.getElementsByTagName("input")(27).NextSibling
eUOM1.Focus
eUOM1.Click

Part 2 was to choose and click the value, like this (*actual element name changed):

Dim eUOM2 As MSHTML.HTMLHtmlElement
Set eUOM2 = ie.document.getElementsByName("[*PutNameHere]")(0)
eUOM2.Value = "EA"
eUOM2.Click

Here are references:refs

回答5:

Function SetSelect(s, val) As Boolean

'Selects an item (val) from a combobox (s)
'Usage:
'If Not SetSelect(IE.Document.all.Item("tspan"), "Custom") Then
'something went wrong
'Else
'continue...
'End If

Dim x As Integer
Dim r As Boolean
r = False
For x = 0 To s.Options.Length - 1
If s.Options(x).Text = val Then
s.selectedIndex = x
r = True
Exit For
End If
Next x

SetSelect = r
End Function

回答6:

Try this code :

doc.getElementById("xs_r_gender").value = "Male"
doc.getElementById("xs_r_gender").FireEvent("onchange")

回答7:

You can do something like this:

doc.getElementsByName("xs_r_gender").Item(1).Selected=True

or

doc.getElementById("xs_r_gender").selectedindex = 1

Where 1 is the male option (in both cases).

If the dropbox needs to fire some event in order to aknowledge your choice, it is likely that it will be the "onchange" event. You can fire it like so:

doc.getElementById("xs_r_gender").FireEvent("onchange")

If you ever want to be able to select an option based on the option's text you can use the function given by Lansman (here) . Based on the same answer, if you want to call the option by it's value property (instead of the text, you can just change the line If xComboName.Options(x).Text = xComboValue Then to If xComboName.Options(x).value = xComboValue Then).

This should cover all bases.

回答8:

You can try the querySelector method of document to apply a CSS selector of option tag with attribute value = 'male':

doc.querySelector("option[value='male']").Click

or

doc.querySelector("option[value='male']").Selected = True

回答9:

Copy from Here till last line:

Sub Filldata()

Set objShell = CreateObject("Shell.Application")

IE_count = objShell.Windows.Count

For X = 0 To (IE_count - 1)

On Error Resume Next    ' sometimes more web pages are counted than are open

my_url = objShell.Windows(X).document.Location

my_title = objShell.Windows(X).document.Title

   If my_title Like "***Write your page name***" Then

    Set IE = objShell.Windows(X)

        Exit For

    Else

        End If

    Next

With IE.document.forms("***write your form name***")

' Assuming you r picking values from MS Excel Sheet1 cell A2

i=sheet1.range("A2").value 

.all("xs_r_gender").Item(i).Selected = True

End with

End sub