Identifying a web table in a VBA web query

I need to extract data from a table on a web page but i don't know what to call it. I inherited a workbook which does this for another site and identifies the table as "1". Please take a look at this page and let me know if possible what to call the Activity Log table (about halfway down the page). Ideally I want to retrieve the data under PAST FLIGHTS.


.WebSelectionType = xlSpecifiedTables
.WebTables = "1"

Open in new window


John CarneyReliability Business Tools Analyst IIAsked:
Who is Participating?
QlemoConnect With a Mentor Batchelor, Developer and EE Topic AdvisorCommented:
For example, you add a reference to  "Microsoft XML" (arbitrary release). then use something like
Dim xml As New XMLHTTP   ' might be XMLHTTP60 or similar

xml.Open "GET", "https://etc.pp/xml/something.jsp?parm1=" & parm1, False
Data1 = xml.responseXML.getElementsByTagName("type_id").Item(0).text

Open in new window

The access methods getElementsByName aso. are the usual HTML DOM ones you can use with a HTML document.
Depending on the XML result there might be more direct methods, above is a fragment for parsing EE questions ...
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
What I usually do as a quick test is to create a new web query with the URL, and then see if there is a yellow square with an error displayed for the corresponding table. If so, Excel is able to detect the table.
Gustav BrockCIOCommented:
Problem is, that it is not a web table.
You'll have to load the page and search the DOM for <div class="flightPageDataTable">

Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

Neil FlemingConnect With a Mentor Independent consultantCommented:
I notice near the top of the page source for your page there is a note that reads:

" Data robots and collection agents should use"

If you look at the page indicates that Flightaware has an API that will let you retrieve data without having to read an HTML table in a browser.

As Gustav notes, the page you are trying to access is built by javascript, so it is a pain to access the resulting DOM.

Hope this helps.
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
I absolutley agree. Certainly any XML API is to be preferred over scraping HTML ...
John CarneyReliability Business Tools Analyst IIAuthor Commented:
Thanks for all your help! So, how do you make an XML API in Excel with VBA?
TracyVBA DeveloperCommented:
No comment has been added to this question in more than 21 days, so it is now classified as abandoned.

I have recommended this question be closed as follows:

-- Qlemo (https:#a42375856)
-- Neil Fleming (https:#a42373316)

If you feel this question should be closed differently, post an objection and the moderators will review all objections and close it as they feel fit. If no one objects, this question will be closed automatically the way described above.

Experts-Exchange Cleanup Volunteer
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.