使用 excel vba 更改网站上下拉菜单的值

Using excel vba to change the value of a dropdown menu on a website

提问by Marcus C

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.

我正在编写一个 Excel 宏来填写网站上的表格。我已经编写了足够轻松地填充文本框的代码，并找到了选择单选框的代码，但是我在从下拉菜单中选择信息时遇到了问题。

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.

示例“性别”：

组合框有三个选项：

Select / Male / Female

我已经尝试了一些变化：

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

...但没有运气。

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.

谢谢。

回答by Marcus C

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

doc.getElementById("xs_r_gender").selectedindex=1
似乎可以解决问题。（其中1代表男性）

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!!!

虽然这意味着我需要进行大量查找以确定下拉列表中项目的值。（对于 Sex 来说很容易，其中只有两个选项，但我有一些组合框最多包含 50 个选项）。如果有人知道更快的解决方案，那就太好了。与此同时，我会开始整理一些桌子！！！

thanks.

谢谢。

回答by Marcus C

Try below code assuming doc = ie.document

假设 doc = ie.document 尝试下面的代码

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

回答by Lansman

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

回答by Longrifle

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

谢谢堆栈，对我有用！我操作 IE HTML 组合框下拉菜单的解决方案原来是两部分。

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

第 1 部分是单击下拉菜单，代码如下：

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):

第 2 部分是选择并单击值，如下所示（*实际元素名称已更改）：

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

Here are references:refs

这里是参考：refs

回答by QHarr

You can try the querySelectormethod of documentto apply a CSS selector of optiontag with attribute value = 'male':

您可以尝试应用带有属性的标记的 CSS 选择器的querySelector方法：documentoptionvalue = 'male'

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

or

或者

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

回答by kenneth

Try this code :

试试这个代码：

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

回答by jony

You can do something like this:

你可以这样做：

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

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

or

或者

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

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

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

其中 1 是男性选项（在两种情况下）。

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:

如果保管箱需要触发某个事件以确认您的选择，则它很可能是“onchange”事件。你可以像这样启动它：

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

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 Thento If xComboName.Options(x).value = xComboValue Then).

如果您希望能够根据选项的文本选择一个选项，您可以使用 Lansman 提供的功能（此处）。基于相同的答案，如果您想通过它的 value 属性调用该选项（而不是文本，您可以将该行更改If xComboName.Options(x).Text = xComboValue Then为If xComboName.Options(x).value = xComboValue Then）。

This should cover all bases.

这应该涵盖所有基础。

回答by kazulius

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

回答by Govind Pandey

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