How to fill in html autocomplete fields using vba?

user_10888586 0
Viewed 1356

I am writing a macro that enter data into a online calculator and get the computed results

Link: https://www.icao.int/environmental-protection/CarbonOffset/Pages/default.aspx

I am trying to fill the values of "from city/airport" in the website.

Here is my Code:

Sub get_CO2()
 Dim ie As Object
  Set ie = New InternetExplorerMedium
  ie.Navigate "https://applications.icao.int/icec"
  Do While ie.ReadyState <> 4: DoEvents: Loop
  ie.Visible = True
  With ie.Document
      .getElementById("passengerNo").Value = 7
      .getElementById("select1").selectedIndex = 1
      .getElementById("select2").selectedIndex = 1
      ' .getElementByName("frm1").Value = "XXX"
      ' .getElementByName("to1").Value = "XXX"
      .getElementById("computeByInput").Click
   End With
End Sub

It is doesnt work. It shows "object do not support this property or method" How can i fill in this kind of types? What function can i use? If the airport code is not known, is there anyway to enter the name of the country and just pick to first option showing up?

web-scraping vba excel html
2 Answers

I have figured out how to do this with vba. EDIT: complete code requested

iedoc.getElementsByName("frm1")(0).innerText = "somecountry"            ' enter string of country
Set li_arr = iedoc.getElementById("ui-id-1").getElementsByTagName("li")
Do While li_arr.Length = 0: DoEvents: Loop     ' wait for the drop down menu to come up
li_arr(0).Click                                ' now click the first option
user_108885860

If using browser I would go with Selenium basic automation as it is easy to trigger the events associated with the input fields. Note I am using the iframe src url direct to avoid having to navigate the iframe containing the form. After installing selenium you need to ensure latest Chrome and Chromedriver with chromedriver.exe in the same folder as the selenium executables. Also, go vbe > tools > references and add a reference to selenium type library.

It is also possible, I think, to do a series POST xmlhttp request provided you can grab and pass on the right cookies.

The lines in code below 

.FindElementByCss("#ui-id-1 li.ui-menu-item")

and

.FindElementByCss("#ui-id-2 li.ui-menu-item")

select the first list element in the dropdowns.

The drop down values are generated continuously from JSON repsonse to POST requests as you type. I have sent the entire required airport string in one go to ensure the first item is the desired.

Option Explicit
Public Sub GetInfo()
    Dim d As WebDriver
    Set d = New ChromeDriver
    Const Url = "https://applications.icao.int/icec"
    With d
        .Start "Chrome"
        .get Url
        .FindElementByCss("[name=frm1]").SendKeys "AAD"
        .FindElementByCss("#ui-id-1 li.ui-menu-item").Click
        .FindElementByCss("[name=to1]").SendKeys "MGQ"
        .FindElementByCss("#ui-id-2 li.ui-menu-item").Click
        .FindElementById("computeByInput").Click

        Stop   'delete me later
        .Quit
    End With
End Sub
user_62412350
Related