Extract Text from Web via VBA

[rdormond]

Hello All,

I am having trouble being able to capture what I need from an HTML website to extract data for my VBA code.

My code automatically uses a webpage to search the distance between two airports. That part works fine, extracting the actual value of the distance is giving me problems.

The website I am extracting data from is

http://www.flightmanager.com/content/timedistanceform.aspx

and my code inputs LAX and ATL as the airports.
The data I want to retrieve is: "1,946.88"
And that could be found where it says "Distance," it should look like this...
Distance: 1,691.43 (NM) / 1,946.88 (MI) / 3,132.53 (KM)

Sub parse_distances()


Dim air1, air2 As String
air1 = "ATL"
air2 = "LAX"
' variables are here to check if this code works, DELETE later


Set objIEBrowser = CreateObject("InternetExplorer.Application")
    '   open Internet Explorer


   objIEBrowser.Visible = True
   '    allows you to see the webpage


   objIEBrowser.Navigate2 "http://www.flightmanager.com/content/timedistanceform.aspx"
   '    opens up the website I will use to find the airport distances
   
   Do While objIEBrowser.Busy
   Loop
   Do While objIEBrowser.readyState < 4
   Loop
   '    prevents anything else from happening while the site is loading.
   
   Set objPage = objIEBrowser.document
   '    idk what this is, i'm just trying to follow similar code from an online source
   
   Set Airport1 = objPage.getElementById("ctl00_ContentPlaceHolder1_txtDepartureICAO")
   Airport1.Value = air1
   Set Airport2 = objPage.getElementById("ctl00_ContentPlaceHolder1_txtArrivalICAO")
   Airport2.Value = air2
   '    Inputs the airport names into the search engine.
   
   Set clickCalcDistButton = objPage.getElementById("ctl00_ContentPlaceHolder1_BtnSubmit")
   clickCalcDistButton.Click
   
   Do While objIEBrowser.Busy
   Loop
   Do While objIEBrowser.readyState < 4
   Loop
   '    prevents anything else from happening while the site is loading.
   
   Dim dist As String
   dist = IE.Dcoument.getElementsByClassName("std5")(1).innerText
   ' THIS IS WHERE I'M HAVING TROUBLE.


End Sub

Tips and help are always appreciated!

 

[ukmikeb]

This line :-

 dist = IE.Dcoument.getElementsByClassName("std5")(1).innerText

is incorrect it should be :-

 dist = IE.Dcoument.getElementsByClassName("td5")(10).innerText

you may have to change the element from 10, either up or down.

hth

 

[rdormond]

Thank you for the response, I also noticed a spelling error in that line of code you mentioned and fixed it. So I've tried the following from element 0 to 15 to no avail.

[COLOR=#333333] dist = IE.document.getElementsByClassName("td5")(10).innerText[/COLOR]

Any other suggestions you might have?

 

[ukmikeb]

Hi

That line needs to be :-

  dist = objPage.getElementsByClassName("td5")(10).innerText

Apologies that I didn't spot either the spelling mistake or that fact that it was referring to the wrong object.

May I suggest changing all instances of the following :-

   Do While objIEBrowser.Busy
   Loop
   Do While objIEBrowser.readyState < 4
   Loop

to :-

   Do While objiebrowser.Busy: DoEvents:  Loop
   Do While objiebrowser.ReadyState < 4: DoEvents:  Loop

to enable the successful completion of those statements.

Also objPage is defined so that you don't have to refer to objIEBrowser.Document both when you are setting the fields on and retrieving data from the webpage.

hth

 

[ukmikeb]

And to get the distance out as a standalone figure as per OP :-

    dist = Trim(Left(Split(objPage.getElementsByClassName("td5")(10).innerText, "/")(1), 10))

hth

 

[rdormond]

Mike,

Your advice and corrections worked wonderfully! Thank you for the help, I truly appreciate it!

-Ryan