Scraping data from a website table

Longshot, try https:// instead of http://

Hi Kersplash, already using https://. Thanks.

Where does the code stop at?

It stops at this code: name = Trim(ele.getElementsByTagName("td")(2).innerText)

The page probably hasn't finished loading when this executes, so the elements probably don't exist yet:

Please Login or Register  to view this content.

This assumes that you have updated the code for the website you are working with, if you haven't, you'll need to update the above to match the page you're working with

Hi Kyle123, the code has already been updated for the website. or could that be something to do with a"show more" button on the webpage? When the webpage is fully loaded, it initially shows a list of around 20 items on the page and there is the "show more" button at the bottom of the page to display more items.

No idea without seeing it. My money would be on the page not having loaded when the code runs

Are you still using Excel 2010?

Hi Kersplash, I am using excel 2016. Thanks.

Maybe have a look at Power Query (Get & Transform) for website scraping.

Hi Kersplash, already tried the Power query but somehow excel couldn't connect to the webpage.

Hi Kyle123,

Below is the HTML code that I see from the website.

(div class="c-table-container")
(table)
(thead)(/thead)
(tbody)
(tr)
(td class="c-table-custom")1780(/td)
(td class="c-table-custom")1DDF(/td)
(td class="c-table-custom")8788(/td)
(/tr)
(/tbody)
(/table)
(/div)

When my code runs, it waits until the page is fully loaded.

Any other suggestions?

Your code doesn't wait until the site is fully loaded. It waits until all the data is received - that's not the same thing. It is increasingly common for websites to build elements with JavaScript AFTER the page has loaded, your code does not wait for this.

In the sample you have posted above, where did you get that from? The developer tools or View source? They are not the same thing, developer tools will show you the html as it is now ie after the javascript whereas view source will show you the actual html your code is using.

Hi Kyle123, oh I understand you now. I got the code from view source. In that case, is there any way I can modify my code above to wait until the page is fully loaded?

If you got it from View source, you don't need to.

However, I've just spotted a mistake in your code:

Please Login or Register  to view this content.

Should be

Please Login or Register  to view this content.

Hi Kyle123, You were correct. When I run the code again, the webpage gets opened but it doesn't wait till it is fully loaded. Is there any way I can make the code to wait until the page is fully loaded? Thank you.

If the information you need is in the source, then you don't need to. It's slowing your code down unnecessarily - though you aren't bothered about performance since you're using IE, so you just need to add a loop that keeps checking whether your element exists.

After hours of searching for the answer on the web, I realized that the webpage I am trying to pull the data from does not load any data when it is opened with IE (it loads the full list when opened with firefox). So after all, it had something to do with the web browser not the VBA.

And I found out that other web browsers doesn't support VBA script. I am not sure what to do from here. Any suggestions?

What's the url?

Its https://icorating.com/ico/upcoming/ .

And which data are you wanting? There's a lot there under show more at the bottom

I am trying to get the full list of all the item names on the page (including the list under the show more). Thanks.

Like this, try it in a new blank workbook first:

Please Login or Register  to view this content.

Hi Kyle123, sorry about the late reply. Your coding does what I wanted to achieve Thanks. You are a legend!!

Hi Kyle123, Once again, thank you so much for your help. I just have few more questions to ask you if that's ok.

If I want to copy the rest of the data on the webpage, not just the company name (i.e. startup name, ico start, pre-start date, ..., goal), how can I modify your coding to do so? I just spent 4 hours to understand how your code works so I can tweak the code to achieve what I want to achieve (As I am still a newbie to VBA and JSON) but unfortunately I was out of luck but it was a good learning curve.

One more data i am trying to get from the website is the URL address that's linked to the name of each company on the page (i was able to do this on other websites using my own VBA script but obviously mine didn't work on this website as the page was not properly being loaded on IE).

Any suggestions or tips would be much appreciated.

Hi YasserKhalil, Awesome. It does the trick. Just urious where did you get those 'name, ticker, name_short, link' strings from? They are obviously not on the webpage and also in their HTML code? What I am trying to do is to copy all the data (start and end date, rating and so on) on the page to excel including the link url (which you have already made it happen). I am trying to understand how this works so i can apply it to other projects I am currently working on if that's ok. Thanks.

Awesome. you are a legend. much appreciated. there goes my weekend :P. Thanks

Hi YasserKhalil, Just two more questions. When I added few more strings to the code (i.e. "goal" and "presale_start_date"), I get the following error message:

Run-time error 9":
Subscript out of range

And also how did you find your suggested URL link? https://icorating.com/ico/upcoming/l...l=true&search=

Many thanks.

Sure.

Please Login or Register  to view this content.

I am getting error on the last two strings:

Please Login or Register  to view this content.

Thank you.

It returns the value of "20 instead of $20,000,000.

I know this is a bit dirty way around but I can still work with it unless there is a cleaner way to do this.

Please Login or Register  to view this content.

I am still trying to figure out how to get the JSON Url (or text response view) as I have number of other websites I am trying to pull more data from. Any idea? Thanks.

You really want to be parsing the JSON properly if you're wanting multiple things, try this module: https://github.com/VBA-tools/VBA-JSON