Our training courses
AI - Articial Intelligence Azure C# Excel Microsoft 365 MySQL Power Apps Power Automate Power BI and DAX Python Report Builder SQL SQL Server VBA Macros

Other training resources
Tips and tricks Guides and tutorials Blogs Exercises YouTube tutorials YouTube shorts Test your skills Consultancy Courseware Self-paced courses Newsletters Publications

Our training venues
London Manchester Across the UK At your site Online

Why we are different
Hundreds of published reviews Small class sizes Outstanding courseware Online classroom (Almost) no cancellations Wise Owl trainers only Genuine integrity Invoices after training 30+ years of Wise Owl Our top 100 clients

Details for DutchWoody

DutchWoody has participated in the following threads:

Added by DutchWoody on 12 Mar 2021 at 07:24

Hi Andrew,

Thank you so much for your reply to my post under Excel VBA Part 47.1. I've managed to apply and adjust the code scroll through all pages and tabs on the AustralianSuper site. When I tried to use a similar approach on another website, I seem to be running into a challenge. On this page https://www.pfzw.nl/over-ons/zo-beleggen-we/waarin-we-beleggen/overzicht-aandelen.html I want to copy the table (like with the AustralianSuper). But all of these FindElementByXXX don't seem to find the table, while in the inspector I can see it there clearly:

 Set PFZWTable = ch.FindElementByClass("data-table style-scope p-data-table")
 Set PFZWTable = ch.FindElementByCss(".data-table.style-scope.p-data-table")
 Set PFZWTable = ch.FindElementByTag("Table")
 Set PFZWTable = ch.FindElementsByXPath("//*[@id='tabledatasource-642984829']//div/div/div/div/table")

Inspector element: <table class="data-table style-scope p-data-table" style="min-width: 830px">, XPath //*[@id="tabledatasource-642984829"]//div/div/div/div/table

I've also given the page a little while to load (ch.Wait "3000"), just in case it needed time to load. I seem to be struggling with FindElementBy the above options in general. On other pages, the only FindElement that works for me is FindElementByLinkText, and in one case ch.FindElementByCss(".next") to click the Next button. 

What am I doing wrong? Am I missing something? If an element seems to have the tag 'table' why does ch.FindElementByTag("Table") when there is only one table tag on the page? The same for FindElementByClass("data-table style-scope p-data-table"). I've watch a lot of (but admittedly not all of) your video 57.4, but still can't seem find what I'm doing wrong.

Your guidance on this is really appreciated! 

Best,

Added by DutchWoody on 08 Mar 2021 at 15:25

Hi Andrew,

You tutorial is fantastically useful! It made a once daunting task appear to be within the realms of possibility! 

However, I'm struggling to get the code to work for a specific website. The getElementsByTagName isn't picking up the table on this site. Could you let me know what I'm doing wrong? Is it because the site is .aspx? Essentially, my next step will hopefully be to get the data per row ("tr"), but I can't even get the table to work.

Currently I have this:

Sub AustralianSuperRetriever()

Dim XMLPage As New MSXML2.XMLHTTP60
Dim HTMLDoc As New MSHTML.HTMLDocument

    ' XMLPage.Open "Get", "https://x-rates.com/table/?from=GBP&amount=5", False
    XMLPage.Open "Get", "https://portal.australiansuper.com/investments-and-performance/what-we-invest-in/superannuation-premixed-investment-options/high-growth.aspx", False

XMLPage.send

HTMLDoc.body.innerHTML = XMLPage.responseText

ProcessHTMLPage HTMLDoc

End Sub

Sub ProcessHTMLPage(HTMLPage As MSHTML.HTMLDocument)


Dim HTMLTable As MSHTML.IHTMLElement
Dim HTMLTables As MSHTML.IHTMLElementCollection

Set HTMLTables = HTMLPage.getElementsByTagName("Table") '- this doesn't work

'Set HTMLTables = HTMLPage.getElementsByTagName("tr") 'this one doesn't work. TR refers to table row.

'Set HTMLTables = HTMLPage.getElementsByClassName("fn-table-feeder-trigger") '- this one works!

'Set HTMLTables = HTMLPage.getElementsByClassName("fn-table-container table-container") '- this one also seems to work

For Each HTMLTable In HTMLTables

Debug.Print HTMLTable.className, HTMLTable.ID, HTMLTable.innerText, HTMLTable.tagName, HTMLTable.Title 'noIDs

Next HTMLTable


End Sub

 

Any help will be really appreciated! 

 

Best,

Head office

Kingsmoor House

Railway Street

GLOSSOP

SK13 2AA

London

Landmark Offices

99 Bishopsgate

LONDON

EC2M 3XD

Manchester

Holiday Inn

25 Aytoun Street

MANCHESTER

M1 3AE

© Wise Owl Business Solutions Ltd 2024. All Rights Reserved.

End of small page here
Please be aware that our website uses cookies!
I'm OK with this Tell me more ...