Sometimes, you may need to scrape some structured data from the internet with Excel. Honestly, Excel. Honestly, Excel should be your last weapon of choice there, as far as it is really not a web-scraping software. Still, it is quite possible, if you give it a try.
What you need is some understanding about how HTML works, its structure and some advanced VBA skills. Let’s say you would like to the title and the link of the VBA books on Amazon.com. Then, you open Amazon.com with a search in it like this one:
and start examining the browser’s elements with F12. After some time, you realize that the element you need are the following 2 classes:
- a-link-normal s-access-detail-page s-color-twister-title-link a-text-normal
How do you realize this? By moving your mouse to the elements and seeing what is being highlighted (I use Google Chrome for this). Then the code, which prints the books’ titles and their links is quite short:
Dim appIE As Object
Set appIE = CreateObject("InternetExplorer.Application")
.Visible = True
Do While appIE.Busy
Dim allData As Object
Set allData = appIE.document.getElementById("s-results-list-atf")
Dim book As Object
For Each book In allData.getElementsByClassName("a-link-normal s-access-detail-page s-color-twister-title-link a-text-normal")
Yup, it prints everything in the immediate window:
Seems easy and nice. Then why is not VBA a good tool for scraping?
Well, if one decides to prints the prices of the books, an additional search for the prices and their class should be carried out. Additionally, not all books in Amazon have all prices – e.g. some are not available on Kindle and some are only on Kindle. This means, that a simple loop through the prices should be a bit meaningless, they should be mapped to the books. Which is possible, but takes some time to be written. Then another problem pops up – amazon has many pages of results:
which is quite tough – you have to think about some logic that clicks on the pages and scrapes all the data again. This is not rocket science, but still requires some time. And probably some additional code, which would be hardly reusable anywhere else. Thus, one may really say that there should be other, better ways to do it. The inspiration for this article came to me after studying for UiPath certification – I have noticed that with that software this task is literally a 5 minute job with no coding required. Thus, I have decided to take a look how quickly I can do something like that in VBA. Well, it was quick (I would not lie), but I was quite picky when choosing the task – I did not write any prices nor additional information and I have decided not automate the Next clicking.
So, take home message from this article – there is an automation software on the market which does not require any programming for standardized tasks like the one above.