Using VBA Excel to navigate a website by changing dropdown value

Question

I am writing an Excel macro that will navigate to a utility company website and then download all of the statements linked to that account. To get to the statement download page, I need to navigate to each account's summary page. All of the accounts are listed in a dropdown - when I manually click a different account from the list, the page updates to the new account. Using the tip I got from this question, I am able to get the macro to change the dropdown value. The problem is, even though it chooses a new value from the list, the page doesn't update to the new account. Unfortunately, there is no direct link to each account's page as it looks like the dropdown passes a value to a script. I tried copying the URL down from different accounts, but no matter what account it's on the URL is the same. Here is VBA code in question:

Do While i < intNumberAcct
    ie.document.getElementById("ctl00_PageContent_AccountDropDown_BillingAccountsDropDown").selectedindex = i
    Call downloadStatement
    i = i + 1
Loop

I tried refreshing IE after it changes the dropdown value, but when it refreshes it reverts back to the original value in the dropdown. I also tried ie.document.getElementById("ctl00_PageContent_AccountDropDown_BillingAccountsDropDown").Click after it selected the new index, but it didn't have any effect.

Here is the html source code of the dropdown - I removed a large chunk of the accounts so it wouldn't be so long and obfuscated the account numbers and addresses with 'x':

<table style="width: 100%" cellpadding="0" cellspacing="0">
<tr>
    <td valign="top" class="AccountDropDownLabelCells" style="height: 25px;">
        <strong>Billing Account:</strong>
    </td>
    <td valign="top" style="text-align: left" align="left">
        <select name="ctl00$PageContent$AccountDropDown$BillingAccountsDropDown" onchange="javascript:setTimeout(&#39;__doPostBack(\&#39;ctl00$PageContent$AccountDropDown$BillingAccountsDropDown\&#39;,\&#39;\&#39;)&#39;, 0)" id="ctl00_PageContent_AccountDropDown_BillingAccountsDropDown" style="width:250px;">
          <option value="xxxxx">xxxxx(16 xxxxx Dr)</option>
          <option selected="xxxxx" value="xxxxx">xxxxx(18 xxxxxDr)</option>
          <option value="xxxxx">xxxxx(20 xxxxx Dr)</option>
          <option value="xxxxx">xxxxx(22 xxxxxDr)</option>
          <option value="xxxxx">xxxxx(28 xxxxx Dr)</option>
          <option value="xxxxx">xxxxx(30 xxxxxDr)</option>
          <option value="xxxxx">xxxxx(34 xxxxxDr)</option>
</select>
        &nbsp;
    </td>
</tr>


</table>

Any ideas on how to get the page to update to the new account after selecting it in the dropdown would be greatly appreciated.

When you (personally) change the account in the dropdown box does the page refresh? Also could you expand your example of the "select" box to include the "form" tag. — NickSlash
– NickSlash, Commented May 2, 2013 at 21:37
Since you have obfuscated the names I assume this is confidential data. Presumably you have entered a password to get access to this information. The web site needs to be able to link subsequent transmissions to you. A common technique is to allocate a session id that is included in every subsequent transmission. Your code will have to simulate that linking technique otherwise the website will assume every transmission is a new enquiry. — Tony Dallimore
– Tony Dallimore, Commented May 2, 2013 at 23:42
The tip from Santosh worked for me. I haven't had any issues with the website wanting to reauthenticate. — Michael
– Michael, Commented May 3, 2013 at 17:01

Santosh · Accepted Answer · 2013-05-03 00:45:36Z

3

Once you set the index of dropdown you need to trigger the change event.

select name="ctl00$PageContent$AccountDropDown$BillingAccountsDropDown" onchange="javascript:setTimeout('__doPostBack(\'ctl00$PageContent$AccountDropDown$BillingAccountsDropDown\',\'\')', 0)" id="ctl00_PageContent_AccountDropDown_BillingAccountsDropDown" style="width:250px;"

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

answered May 3, 2013 at 0:45

Santosh

12.4k4 gold badges43 silver badges74 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Using VBA Excel to navigate a website by changing dropdown value

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related