Excel VBA change option in the HTML select tag

Question

Recently, I posted the question vbscript capture text in the HTML select option tag. This helped me tremendously on an Internet Explorer platform. However, I have a new project with a bit more complex of a web site. The web site has multiple nested tables that are formatted to hold form elements. My team and I have figured out how to get and set data within most of the elements except the drop down box (select tag). We are able to get the text from the drop down, but we can't change the value. Instead, all the options are wiped clear and the whole drop down is blank.

Here's a revised version of the code for the web site as there is much more code involved, but only the pertinent code is shown:

<html xmlns="http://www.w3.org/1999/xhtml">
  <head>...</head>
  <body>
    <form id="form1" action="AssignOFS.aspx" method="post">
      <div class="aspNetHidden">...</div>
      <div class="aspNetHidden">...</div>
      <table width="100%" align="center" cellspacing="0">
        <tbody>
          <tr>
            <td>
              <table width="100%" align="center" cellspacing="0">...</table>
            </td>
          </tr>
          <tr>
            <td>
              <table width="100%" align="center" cellspacing="0">
                <tbody>
                  <tr>...</tr>
                  <tr>...</tr>
                  <tr class="tr_data">...</tr>
                  <tr>
                    <td align="center">
                      <div id="pnlBtn1">...</div>
                      <div id="pnlView">
                        <table width="100%" align="center" cellspacing="0">
                          <tbody>
                            <tr>...</tr>
                            <tr>...</tr>
                            <tr class="tr_data">
                              <td align="center">...</td>
                              <td valign="top">...</td>
                              <td valign="top">...</td>
                              <td valign="top">...</td>
                              <td nowrap="" valign="top">...</td>
                              <td align="left">...</td>
                              <td align="center">...</td>
                              <td align="center">
                                <select name="user" class="txt_input1" id="user" onchange="javascript:addUser(227);">
                                  <option value="">--Select User--</option>
                                  <option value="0123">Amy Jones (000456321)</option>
                                  <option value="0432">Brian Wyatt (000745632)</option>
                                  <option value="0345">Carl Lister (000874563)</option>
                                  <option value="0654">Daniel Michaels (000987456)</option>
                                  <option value="0567">Elizabeth Sweeny (001456321)</option>
                                  <option value="0876">Fran Tarris (001745632)</option>
                                  <option value="0789">Gail McMurphy (001874563)</option>
                                  <option value="1098">Hannah Barisce (001987456)</option>
                                </select>
                              </td>
                            </tr>
                          </tbody>
                        </table
                      </div>
                    </td>
                  </tr>
                </tbody>
              </table>
            </td>
          </tr>
        </tbody>
      </table>
    </form>
  </body>
</html>

To be able to get the text from the drop down, we used the class name txt_input1 after locating the node within the table that holds the drop down. This code will give us the entire list within the drop down:

objIE.Document.getElementsByTagName("table")(0).getElementsByTagName("td").Item(64).GetElementsByClassName("txt_input1")(0).innerText

I've tried changing the value of the select drop down from "--Select User--" to one of the names in the list with the following code:

objIE.Document.getElementsByTagName("table")(0).getElementsByTagName("td").Item(64).GetElementsByClassName("txt_input1")(0).innerText = "Carl Lister"

This resulted in the blank drop down. If I changed the ending to .Value, it only removes "--Select User--" from being shown. It still doesn't change the value to "Carl Lister". Is there something we're missing when it comes to selecting an option in a select drop down within nested tables?

freginold · Answer 1 · 2017-08-30T18:29:15.563

3

For a select element, the best way to change the displayed value is to "select" one of the option elements that's part of the menu. To do that, you could change your code like this:

objIE.Document.getElementsByTagName("table")(0).getElementsByTagName("td").Item(64).GetElementsByClassName("txt_input1")(0).childNodes(x).selected = true

The code above would select the child node (the option element) at index x and programmatically select it, so that value would be displayed in the select element. The index for the options starts at 0, so for x = 0 that would select the first option (in this case, Amy Jones).

You could also simplify the above code to just read:

objIE.Document.getElementsByClassName("txt_input1")(0).childNodes(x).selected = true

Or you could even do it by id since you have an ID assigned to the menu:

objIE.Document.getElementById("user").childNodes(x).selected = true

If you wanted to select an employee name from the drop down list, you could loop through the existing options and find the correct one like this (code adapted from the VBA code in this question):

Dim el As IXMLDOMNode
Dim childNode As IXMLDOMNode

el = objIE.Document.getElementById("user")

For Each childNode in el.ChildNodes
    If InStr(childNode.innerText, emplName) Then
        childNode.selected = true
        Exit For
    End If
Next childNode

In this example, emplName is whichever employee you want to select.

(My VBA is a little rusty, so I apologize if any of the syntax is off.)

edited Aug 30 '17 at 18:29

answered Aug 30 '17 at 15:03

freginold

3,946
3
13
28

Okay, so say Amy Jones leaves the company and Isabella Martin gets hired. `x = 0` would not be `Amy Jones` anymore, it would be `Brian Wyatt`, but I may be assigning something to Carl Lister while Amy is still with the company, then, when she leaves, `x = 2` would no longer equal `Carl Lister`, it would now be `Daniel Michaels`. Is there a way of selecting by the person's name instead of the index number? Or are the above codes doing just that and I'm reading into it too much? – Lou Aug 30 '17 at 15:29
Nope, there's definitely a way to do that. It's just a matter of figuring out the best way. For example, the `value` attributes for your options are all 4-digit numbers; are those unique employee identification numbers? If so, you could change the code to target the option with a specific number instead, like `if option.value == '0123'`. That way, the order of employees, if any were added or removed, wouldn't matter. – freginold Aug 30 '17 at 16:48
... or you could do it by employee name, if you wanted to. It would just be a matter of looping through the names that are currently there and selecting the right one, using `innerText`. – freginold Aug 30 '17 at 16:58
No, the employee IDs are in parenthesis. I've used these values before in another program, but that's for info that doesn't change. Employee names, IDs, and possibly the `option value` could change, therefore, I wanted to be able to mimic typing "CAR" for Carl Lister and have it pop up...which does work when typing manually. – Lou Aug 30 '17 at 17:01
For the loop for the employee name, would I need to see if `option value="0345"` equals `Carl Lister`? Not sure how we'd set that up, but that's the option I'd like to go. – Lou Aug 30 '17 at 17:03
@Lou It would be very similar to that. I edited my answer to include a loop you could use to select an option based on employee name (at the bottom of the answer). Does that do what you need? – freginold Aug 30 '17 at 17:18
Getting closer. I don't want to "change" the option of the employee...I don't have that kind of access to the web site. What I am looking for is to be able to choose by name. What is happening is this is a tool for assigning work. The web site has the employee's name at the top. I capture that, then I want to use it to update the `select` drop down so as to automate the assignment process. That's the part I want to change is from "--Select User--" to their name. – Lou Aug 30 '17 at 17:42
@Lou Ah okay, I've updated the `for` loop to do that. Feed it in the employee name and it will find that option and "select" it, so it will be displayed in the `select` element instead of the default "--Select User--" text. – freginold Aug 30 '17 at 18:00
Okay, I gave your code a try. It's saying there's no object for `el.childNodes`, but clearly `objIE.Document.getElementById("user")` should be setting the object. – Lou Aug 30 '17 at 18:19
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/153295/discussion-between-lou-and-freginold). – Lou Aug 30 '17 at 19:30

score 2 · Accepted Answer · answered Aug 31 '17 at 13:16

Thanks, @freginold, for all your help. My team and I came up with a solution that expands upon our already developed solutions.

tdNode = 64
i = 0

For Each objOption In objIE.Document.GetElementsByTagName("table")(0).GetElementsByTagName("td")(tdNode).GetElementsByClassName("txt_input1")(0).Options
    Q(i) = objOption.Text & "-" & objOption.Value
    QNm = QNm & "|" & Q(i)
    i = i + 1
    If objOption.Selected Then
     strWQ = objOption.Text
    End If

    If i > 53 Then
       MsgBox "stuck"
       SetEverythingToNothing
    End If

Next

QNm = QNm & "|=" & strWQ

a = 1
MyArray = Split(QNm, "|")

For a = LBound(MyArray) To UBound(MyArray)
    If InStr(MyArray(a), UserName) Then
        strWQ = MyArray(a)
    End If
Next

SelQ = Mid(strWQ, InStr(strWQ, "-") + 1, Len(strWQ) - InStr(strWQ, "-"))

For Each objOption In objIE.Document.GetElementsByTagName("table")(0).GetElementsByTagName("td")(tdNode).GetElementsByClassName("txt_input1")(0).Options
    If objOption.Value = SelQ Then
        objOption.Selected = True
        objIE.Document.GetElementsByTagName("table")(0).GetElementsByTagName("td")(tdNode).GetElementsByClassName("txt_input1")(0).OnChange
    Else
        objOption.Selected = False
    End If
Next

Excel VBA change option in the HTML select tag

2 Answers2

Linked