Browse 536 attributed reviews, viewable separately for our classroom and online training

Details for DutchWoody

DutchWoody has participated in the following threads:

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,

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,