Access VBA insert values into webform without predictable element id

I am working on a vba script to insert information from an Access 2010 database into an web form through IE 11. I have Microsoft Internet Controls, Microsoft HTML Object Library and Microsoft Form Library references set.

The purpose is to insert payment information from a database in my office to a client's database by web form. My existing code traverses through two pages of logins. The first page authenticates us as proper users of the client's system. The second page requests information about the account holder. The third page is where I run into problems. Each account holder may have multiple accounts. The third page is dynamically generated and I can't figure out how to grab the proper element id and match the proper id to information in my database so that values can be inserted.

As an example, my database would have the following sample data about an account holder with 4 accounts:

Anum        Pay
8677229     $25
10289183    $40
11981680    null
13043481    $2

The third page, where I would like to insert information, would have input elements that look like this:

<input name="myaccountpage:myAccountsBlock:accountForm:contactDisplayArea2:0:paymentAmount" class="paymentInput" id="myaccountpage:myAccountsBlock:accountForm:contactDisplayArea2:0:paymentAmount" style="text-align: right;" onkeyup="handlePaymentChange(this,'13043481')" type="text">

<input name="myaccountpage:myAccountsBlock:accountForm:contactDisplayArea2:1:paymentAmount" class="paymentInput" id="myaccountpage:myAccountsBlock:accountForm:contactDisplayArea2:1:paymentAmount" style="text-align: right;" onkeyup="handlePaymentChange(this,'10289183')" type="text">

<input name="myaccountpage:myAccountsBlock:accountForm:contactDisplayArea2:2:paymentAmount" class="paymentInput" id="myaccountpage:myAccountsBlock:accountForm:contactDisplayArea2:2:paymentAmount" style="text-align: right;" onkeyup="handlePaymentChange(this,'8677229')" type="text">

<input name="myaccountpage:myAccountsBlock:accountForm:contactDisplayArea2:3:paymentAmount" class="paymentInput" id="myaccountpage:myAccountsBlock:accountForm:contactDisplayArea2:3:paymentAmount" style="text-align: right;" onkeyup="handlePaymentChange(this,'11981680')" type="text">

The account number (Anum) from my database is found in the onkeyup event in each input box. As you can see, it is not in a predictable order. I want to grab that number and insert the Pay value from my database into corresponding input element. Unfortunately, I don't know how. I have researched .GetElementsByName, htmlelementscollection, and other methods, and none seem to be the key. I also looked at the innertext method, but I am not sure how to invoke it after a good deal of reading.

The VBA, as far as I have it, looks like the below. The SendKeys§ function is a replacement for Sendkeys that I found on the web, and sometimes I send a zero-length string as replacement for a sleep function:

Public Sub Login_Pay2()
'set reference to microsoft internet controls and microsoft html library
'add reference to microsft form library by browsing for FM20.dll in system32
Dim ieApp As InternetExplorer
Dim iePage As HTMLDocument
Dim ieObj As HTMLInputElement
Dim tries As Integer
Dim user As String
Dim pass As String
Dim dnumorig As String
Dim govone As String

user = "myuser"
pass = "mypass"
dnumorig = "mydnum"
govone = "mygov"

Set ieApp = New InternetExplorer
ieApp.Visible = True
ieApp.Navigate "myurl"
'wait for page to load

Do Until ieApp.ReadyState = READYSTATE_COMPLETE
Loop
Set iePage = ieApp.Document

AppActivate ("mywindow")
tries = 0
tryagain:
With ieApp
Set ieObj = iePage.getElementById("username")
If ieObj Is Nothing Then
SendKeys§ "", 750
tries = tries + 1
If tries < 5 Then
GoTo tryagain
Else
Exit Sub
End If
End If
ieObj.Value = user

Set ieObj = iePage.getElementById("password")
ieObj.Value = pass
SendKeys§ "", 40
AppActivate ("mywindow")
SendKeys§ "{TAB}", 40
AppActivate ("mywindow")
SendKeys§ "{RETURN}", 40
End With

Do Until ieApp.ReadyState = READYSTATE_COMPLETE
Loop
Set iePage = ieApp.Document

AppActivate ("mywindow")

tries = 0
tryagain2:
With ieApp
Set ieObj = iePage.getElementById("myaccountpage:manageAccountsBlock:j_id2:searchText")
If ieObj Is Nothing Then
SendKeys§ "", 750
tries = tries + 1
If tries < 5 Then
GoTo tryagain2
Else
Exit Sub
End If
End If
ieObj.Value = dnumorig

Set ieObj = iePage.getElementById("myaccountpage:manageAccountsBlock:j_id2:j_id10")
ieObj.Value = govone
AppActivate ("mywindow")
SendKeys§ "", 40
AppActivate ("mywindow")
SendKeys§ "{RETURN}", 40
End With

'??? at this point I am at page 3 and am stumped.

Thank you for your patience, and any help would be appreciated.

Edit:

I should have noted that the elements I am trying to manipulate are at the end of a larger line of code - example below:

</div></td><td class="dataCell clsCenter " id="myaccountpage:myAccountsBlock:accountForm:contactDisplayArea2:3:j_id63" colspan="1">ACCOUNT TYPE</td><td class="dataCell clsCenter " id="myaccountpage:myAccountsBlock:accountForm:contactDisplayArea2:3:j_id70" colspan="1"> $5,555.55</td><td class="dataCell clsCenter " id="myaccountpage:myAccountsBlock:accountForm:contactDisplayArea2:3:j_id74" colspan="1"> $55.55</td><td class="dataCell clsCenter " id="myaccountpage:myAccountsBlock:accountForm:contactDisplayArea2:3:j_id78" colspan="1"> $555.55</td><td class="dataCell clsCenter " id="myaccountpage:myAccountsBlock:accountForm:contactDisplayArea2:3:j_id81" colspan="1"> $6,666.66</td><td class="dataCell clsCenter " id="myaccountpage:myAccountsBlock:accountForm:contactDisplayArea2:3:j_id85" colspan="1" style="min-width:195px;">$<input id="myaccountpage:myAccountsBlock:accountForm:contactDisplayArea2:3:paymentAmount" type="text" name="myaccountpage:myAccountsBlock:accountForm:contactDisplayArea2:3:paymentAmount" class="paymentInput" onkeyup="handlePaymentChange(this,'11981680')" style="text-align:right" /></td></tr></tbody></table>

Edit 2: Thank you so much. I added the below code, and after tinkering with load times, added a delay, and your code worked to inject one payment amount into one paymentInput element:

Do Until ieApp.ReadyState = READYSTATE_COMPLETE
Loop
Set iePage = ieApp.Document
SendKeys§ "", 2000
AppActivate ("Case Representative - Internet Explorer")

Dim paymentInputElements As IHTMLElementCollection
Set paymentInputElements = iePage.getElementsByClassName("paymentInput")
Dim Anum As String
Anum = "10289183"
Dim pay As String
pay = "40.00"


' Assuming that the account number is in a string variable called Anum
Dim paymentInput As IHTMLElement
' Use this to check if we found the account - initializes to False
Dim bFoundAnum As Boolean
For Each paymentInput In paymentInputElements
    If (InStr(paymentInput.getAttribute("onkeyup"), Anum) > 0) Then
        ' do stuff
        bFoundAnum = True
        paymentInput.Value = pay
        Exit For
    End If
Next paymentInput

' Check we found something
If Not bFoundAnum Then
    MsgBox ("no matching anum")
End If

I will try to mark your answer as accepted if my reputation allows it. If you are willing to keep helping me, I am having some trouble figuring out how I will export multiple Anums and Pay amounts from my database and match those to each matching paymentInput element (each account holder will have one or more Anum to apply payments to). If you have any thoughts on that, I am all ears.

Answers


If you have IE9 or higher installed then HTMLDocument has a getElementsByClassName method which selects every element whose class attribute contains the specified class, so:

Dim paymentInputElements As IHTMLElementCollection
Set paymentInputElements = iePage.getElementsByClassName("paymentInput")

(For IE8, you can achieve the same result by replacing the 2nd line with Set paymentInputElements = iePage.querySelectorAll(".paymentInput"))

We can now iterate through the collection until we find the relevant element:

' Assuming that the account number is in a string variable called Anum
Dim paymentInput As IHTMLElement
' Use this to check if we found the account - initializes to False
Dim bFoundAnum As Boolean
For Each paymentInput In paymentInputElements
    If (Instr(paymentInput.getAttribute("onkeyup"), Anum) > 0) Then
        ' do stuff
        bFoundAnum = True
        Exit For
    End If
Next paymentInput

' Check we found something
If Not bFoundAnum Then
    ' do something - e.g. report an error
End If

You could use a dictionary and querySelector to target by CSS the elements to update. The dictionary keys (Anum) are used to concantate into the CSS selector to target input tags with onkeyup attribute having value equal to the key. The associated dictionary value for the key is used to assign the value.

Option Explicit
Public Sub MakePayments()
    Dim paymentDict As Object
    Set paymentDict = CreateObject("Scripting.Dictionary")

    paymentDict.Add 8677229, "$25"
    paymentDict.Add 10289183, "$40"
    paymentDict.Add 11981680, "null"
    paymentDict.Add 13043481, "$2"

    'Code to get HTMLDocument

    For Each Key In paymentDict.keys
       On Error Resume Next
       iePage.querySelector("input[onkeyup='handlePaymentChange(this,'" & Key & "')']").Value = paymentDict(Key)
      On Error GoTo 0
    Next Key
End Sub

Here is what an example CSS selector would look like using the last key (13043481):


Need Your Help

FluentValidation validator not being called

asp.net-mvc-3 entity-framework-4 partial-classes fluentvalidation

I have a MVC 3 site but am using the non-MVC FluentValidation dll. I've created a validator class and in the constructor put all my RuleFors and then set an attribute on my model class thus

What do the different groovy shell verbosity levels mean?

groovy

org.codehaus.groovy.tools.shell.IO has four verbosity levels but doesn't make it very clear what they do. Which is the System default?