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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Qlemo"Batchelor", 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">

Neil FlemingConsultant and developerCommented:
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.
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Qlemo"Batchelor", 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?
Qlemo"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 ...

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.