Internet Explorer Automation with VBA Input Events

Question

I am trying to use automation from Microsoft Access 2003 to manage Internet Explorer 9 to fill out a form using database data.

Input fires an event in the browser that validates the data and makes the save button visible. If I use sendkeys, the event fires; however, I found sendkeys very unreliable. If I change the value of an element and then use .fireevent ("onchange"), nothing happens - even an error.

My question is: how do I fire an event. Or, how can I find out what javascript is working. Is there a debug type addin for IE that tells me which event is fired? If so, can I run the script itself?

My code is below.

Set IE = CreateObject("internetexplorer.application") IE.Visible = True IE.Navigate "https://extranet.website.com/Planning/Edition/Periodic?language=en" Do While IE.ReadyState <> 4 Or IE.Busy = True DoEvents Loop 'log in If IE.Document.Title = "website- access" Then IE.Document.getElementById("login_uid").Value = "username" IE.Document.getElementById("login_pwd").Value = "password" IE.Document.all("ButSubmit").Click Do While IE.ReadyState <> 4 Or IE.Busy = True DoEvents Loop End If Do While Not RstAvailability.EOF StartDate = RstAvailability!AvailDate IE.Document.getElementById("periodStart").Value = Format(StartDate, "dd mmm yy") IE.Document.getElementById("periodEnd").Value = Format(StartDate, "dd mmm yy") Set LinkCollection = IE.Document.GetElementsByTagName("A") For Each link In LinkCollection If link.innertext = "Add" Then link.Click Exit For End If Next Do While IE.ReadyState <> 4 Or IE.Busy = True DoEvents Loop Set objRows = IE.Document.GetElementsByTagName("tr") If RstAvailability!RoomType = "DTW" Then n = 0 While n < objRows.Length If Trim(objRows(n).Cells(0).innertext) = "Single Room" Then For i = 1 To 7 'objRows(n).FireEvent ("onchange") 'objRows(n).Cells(i).GetElementsByTagName("input")(0).Focus 'SendKeys Format(RstAvailability!roomcount - RstAvailability!RoomsSold, "0") & "{TAB}" objRows(n).Cells(i).GetElementsByTagName("input")(0).Value = Format(RstAvailability!roomcount - RstAvailability!RoomsSold, "0") objRows(n).Cells(i).GetElementsByTagName("input")(0).fireevent ("onchange") Do While IE.ReadyState <> 4 Or IE.Busy = True DoEvents Loop Next i End If n = n + 1 Wend End If Set objButtons = IE.Document.getelementsbyname("savePlanning") objButtons(0).Click Do While IE.ReadyState <> 4 Or IE.Busy = True DoEvents Loop newtime = Now + TimeValue("0:00:10") Do While True If Now > newtime Then Exit Do Loop RstAvailability.MoveNext Loop 

html input fields:

 <tr class="first" roomId="30494" articleId="0" type="Availability" readonly="False"> 

 <div> <span class="roomName"> Single Room </span> </div> 

 <span class="data"> 

 <input id="Availabilities" name="Availabilities" type="text" value="" /> 

 </span> 

 <span class="data"> 

 <input id="Availabilities" name="Availabilities" type="text" value="" /> 

 </span> 

Thanks!

Answer 1

After sweating for several days, the answer was very simple, but almost impossible to find in any documentation on MSDN or elsewhere on the Internet.

Before changing the value of an input field, you can set the focus on this field. After changing the value, you need to set the focus to another field. Events seem to overlook the loss of focus. Therefore, the code should look like this:

  n = 0 While n < objRows.Length If Trim(objRows(n).Cells(0).innertext) = "Family Room" Then For i = 1 To 7 objRows(n).Cells(i).GetElementsByTagName("input")(0).Focus objRows(n).Cells(i).GetElementsByTagName("input")(0).Value = Format(RstAvailability!roomcount - RstAvailability!RoomsSold, "0") Next i objRows(n).Cells(1).GetElementsByTagName("input")(0).Focus End If n = n + 1 Wend 

I found this by looking at some MSDN docs on accessibility for IE9. He advised setting focus for users with disabilities. I just thought I would try, and it will work. Hope this helps someone else.

Dave

Answer 2

If you want (in IE9) put the next line right before the "Next i" line. should it also work, even if you don't lose focus?

objRows (n) .Cells (i) .GetElementsByTagName ("input") (0) .Click