0

I have a simple Excel VBA web scraping sub that I have used for a couple of websites just fine.

The main summary is that it goes to the website, then performs:

    While .Busy Or .readyState <> 4: DoEvents: Wend
    ie.ExecWB 17, 0 '// Select All
    ie.ExecWB 12, 2 '// Copy selection

I then paste that into my excel, and extract my data.

The problem is, that on this website: https://www.legalandgeneral.com/investments/funds/prices-and-reports/daily-fund-prices/ The table with all the data isn't selected when I hit ctrl + A, or with ie.ExecWB 17,0 in the code. If I click on the table and then do ctrl + A it does select the data. I tried using a CSS selector to click the table, then run ie.ExecWB 17,0 but it doesn't work.

Any ideas?

Many thanks

Dave

Dave_1234
  • 3
  • 2
  • The table you want is in an iFrame as own HTML document: https://widgets-lgim.huguenots.co.uk/Consumer-unit-trust-prices-index-tracking-funds Your method to scrape data from a website is a little bit creepy to me. But if it works for you. – Zwenn Jan 26 '21 at 14:06
  • Hi Zwenn. Ah an iFrame. I have googled this and found something here: [link](https://stackoverflow.com/questions/56861132/excel-vba-web-scraping-table-elements-from-a-frameset-and-a-frame) Any ideas if that would work? Thanks, and sorry for creeping you out! :P Dave – Dave_1234 Jan 27 '21 at 12:35
  • 1
    Navigate to "https://widgets-lgim.huguenots.co.uk/Consumer-unit-trust-prices-n-unit-trust" then use `ie.document.querySelector(".prices-table")` to select the table and proceed to use clipboard to copy paste table to sheet: [example](https://stackoverflow.com/a/56450041/6241235) – QHarr Jan 27 '21 at 23:19
  • Ohhh, I see. Sorry I had dismissed that link as something like a CSS selector widget. Many thanks for your help QHarr and Zwenn. I have now found all 5 iFrame src addresses for each tab. I will now follow your example given QHarr to complete my web scraping. Regards – Dave_1234 Jan 28 '21 at 12:38

1 Answers1

0

Both Zwenn and QHarr spotted this, with the table iFrame pointing to 5 different sources.

Hitting 'inspect' on the table, you would see something like this:

<iframe style="border: 0;" src="https://widgets-lgim.huguenots.co.uk/Consumer-> unit-trust-prices-actively-managed-funds" width="1000" height="1900" scrolling="no"></iframe>

So then I found all the other sources by changing the table tab, and inspecting:

https://widgets-lgim.huguenots.co.uk/Consumer-unit-trust-prices-n-unit-trust

https://widgets-lgim.huguenots.co.uk/Consumer-unit-trust-prices-index-tracking-funds

https://widgets-lgim.huguenots.co.uk/Consumer-unit-trust-prices-multi-asset-funds

https://widgets-lgim.huguenots.co.uk/Consumer-unit-trust-prices-fixed-income-funds

https://widgets-lgim.huguenots.co.uk/Consumer-unit-trust-prices-actively-managed-funds

This can now be closed.

Regards

Dave_1234
  • 3
  • 2