CODEWITHSUNDEEP
htmlexceldrop-down-menuvba
Lou
Lou

Reputation: 403

Excel VBA change option in the HTML select tag

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?

Upvotes: 2

Views: 7232

Answers (2)

Lou
Lou

Reputation: 403

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

Upvotes: 2

freginold
freginold

Reputation: 3956

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

Upvotes: 3

Related Questions