Scrape website using Selenium VBA

This post is continuation to Browser automation in Excel using Selenium VBA. Have a look at that post first as it explains how to get started. This post is all about how to scrape website using selenium VBA in Excel and different methods that are used to scrape data and best practices for scraping.

Scrape a blog

Lets get started with a simple example. Lets scrape posts data from this blog codingislove.com. We’ll be scraping post title, URL, Category and Published date.

Scraped data looks like this :

Scrape website using selenium VBA

Simple goal, Go to codingislove.com and scrape data. Lets have a look at code once.

Public Sub scrapeCIL()
Dim bot As New WebDriver, posts As WebElements, post As WebElement, i As Integer, mysheet As Worksheet
bot.Start "chrome", "https://codingislove.com"
bot.Get "/"
Set posts = bot.FindElementsByTag("article")
i = 2
Set mysheet = Sheets("Coding is Love")
For Each post In posts
mysheet.Cells(i, 1).Value = post.FindElementByClass("post-title").Text
mysheet.Cells(i, 2).Value = post.FindElementByClass("post-title").FindElementByTag("a").Attribute("href")
mysheet.Cells(i, 3).Value = post.FindElementByCss(".post-meta > a").Text
mysheet.Cells(i, 4).Value = post.FindElementByClass("published").Text
i = i + 1
Next
End Sub

Code explanation

  1. First, we start a web driver, go to codingislove.com homepage.

  2. Extract all posts to posts element using FindElementsByTag method as all posts have a HTML tag named article in this blog. We can look up HTML elements of a page using Right click > Inspect element in chrome. I’ve defined posts as WebElements. FindElementsByTag method extracts all elements with specified tag.

  3. mysheet is defined as a worksheet, set it to sheetname to which date needs to be extracted. In this case my sheet name is Coding is Love

  4. Now loop through each post in posts and extract post data to mysheet. I’ve used an integer i to loop through cells in sheet.

  5. Post title element has a class name of post-title, post date has a class name of published so we use FindElementByClass method to extract them.

  6. Post URL is available in href attribute of a tag so we use attribute("href") to get post URL. Post category doesn’t have any class or ID or name so we use CSS selector FindElementByCss(".post-meta > a") which means find element with a tag in element with class named post-meta. Read more about CSS selectors here : Getting started with CSS Selectors

Thats it, we’ve scraped simple data from a website using selenium VBA.

Scrape products from E-commerce website

Recently one of the readers of this blog has asked me how to scrape product data from an e-commerce website using selenium VBA. I’m sharing the same code now.

Goal is to extract products from this webpage https://expert.no/tv-og-tilbehor/tv/pl-1862/

Note : This example is purely for learning purpose and not intended for stealing data

Lets have a look at code once.

Public Sub expertscraper()
Dim bot As New WebDriver, myproducts As WebElements, myproduct As WebElement, i As Integer, productnum As String, clicknum As Integer, mysheet As Worksheet
bot.Start "chrome", "https://expert.no"
bot.Get "/tv-og-tilbehor/tv/pl-1862/"
bot.Window.Maximize
i = 2
Set mysheet = Sheets("example2")
Set myproducts = bot.FindElementsByClass("product-container")
For Each myproduct In myproducts
If myproduct.FindElementByClass("product-name").Text <> "" Then
mysheet.Cells(i, 1).Value = myproduct.FindElementByClass("product-name").Text
mysheet.Cells(i, 2).Value = Replace(myproduct.FindElementByClass("product-description-bullets").Text, vbLf, ",")
mysheet.Cells(i, 3).Value = myproduct.FindElementByClass("product-price").Text
i = i + 1
End If
Next
MsgBox ("complete")
End Sub

Code explanation

Above code is pretty much the same as in the previous example. Go to webpage, extract all products, loop through each product and extract data to sheet.

But there is a problem. When page loads first, only 36 products are loaded into page, more products are loaded when see more button in clicked at the end of the page. Every time see more button is clicked, 36 more products are added. Also total number of products is not same always. So we’ve to figure out a way to automate extracting all products without any manual intervention.

I used smart calculations there.

productnum = bot.FindElementById("product-list-count").Text 'find total number of products
productnum = Mid(productnum, 1, Application.WorksheetFunction.Search(" ", productnum) - 1)
clicknum = Round((CInt(productnum) - 36) / 36, 0) 'find number of times more products has to be clicked to get all products
For i = 1 To CInt(clicknum)
bot.FindElementByXPath("//*[@id='product-list-load-more']/div/button").Click
bot.Wait 1000
Next

Total count of products is displayed in the webpage when loaded first, So I extracted total count of products first, subtracted 36 from it and divided by 36 and rounded it to get total number of clicks to be made on see more button to get all products on the page. Then we go for normal loop to extract all the products to excel sheet.

Final code looks like this :

Public Sub expertscraper()
Dim bot As New WebDriver, myproducts As WebElements, myproduct As WebElement, i As Integer, productnum As String, clicknum As Integer, mysheet As Worksheet
bot.Start "chrome", "https://expert.no"
bot.Get "/tv-og-tilbehor/tv/pl-1862/"
bot.Window.Maximize
productnum = bot.FindElementById("product-list-count").Text 'find total number of products
productnum = Mid(productnum, 1, Application.WorksheetFunction.Search(" ", productnum) - 1)
clicknum = Round((CInt(productnum) - 36) / 36, 0) 'find number of times more products has to be clicked to get all products
For i = 1 To CInt(clicknum)
bot.FindElementByXPath("//*[@id='product-list-load-more']/div/button").Click
bot.Wait 1000
Next
bot.Wait 500
i = 2
Set mysheet = Sheets("example2")
Set myproducts = bot.FindElementsByClass("product-container")
For Each myproduct In myproducts
If myproduct.FindElementByClass("product-name").Text <> "" Then
mysheet.Cells(i, 1).Value = myproduct.FindElementByClass("product-name").Text
mysheet.Cells(i, 2).Value = Replace(myproduct.FindElementByClass("product-description-bullets").Text, vbLf, ",")
mysheet.Cells(i, 3).Value = myproduct.FindElementByClass("product-price").Text
i = i + 1
End If
Next
MsgBox ("complete")
End Sub

Best practices of scraping data in excel

Using Selenium is not the best method to scrap data always. It totally depends on the webpage and amount of data to be scraped. For instance, In the above example where we scrape products, Selenium is not the best method for that. That website pulls data from their Json API which is available at https://www.expert.no/Umbraco/Api/Product/ProductsByCategory

Simple HTTP POST requests to their API would be the best method to scrape that website. It hardly takes 5 seconds to scrape 240 products using API method whereas it takes about a minute to scrape the same using selenium VBA and also hangs a bit as excel is single threaded.

Code for API method :

Public Sub apimethod()
Dim xmlHttp As Object, api As String, Json As Object, response As Object, postdata As String, i As Integer, mysheet As Worksheet
myurl = "https://www.expert.no/Umbraco/Api/Product/ProductsByCategory"
postdata = "{'SiteId':2,'CampaignId':0,'CategoryId':1862,'StartIndex':0,'NumberToGet':239,'SortId':5,'IsLoadMore':true}"
Set xmlHttp = CreateObject("MSXML2.serverXMLHTTP")
xmlHttp.Open "POST", myurl, False
xmlHttp.setRequestHeader "Content-Type", "text/json"
xmlHttp.Send postdata
Set Json = JsonConverter.ParseJson(xmlHttp.responseText)
Set mysheet = Sheets("API Method")
i = 2
For Each product In Json("Products")
mysheet.Cells(i, 1).Value = product("Title")
mysheet.Cells(i, 2).Value = product("ShortDescription")
mysheet.Cells(i, 3).Value = product("ManufacturerName")
mysheet.Cells(i, 4).Value = product("Price")
i = i + 1
Next
End Sub

That website uses JSON API and excel doesn’t have built-in support for parsing JSON so I’ve used a JSON Parsing library which is available here – VBA-JSON

Also, In the first example where we scrape blog post data, simple HTTP GET requests can be made to blog homepage and scrape the HTML response.

Wrapping up

I’ll be explaining in-depth about when to use basic HTTP requests, when to use selenium VBA and when to use APIs and how to use them in another post. It requires some basic understanding about client side rendering, server-side rendering, APIs etc. Meanwhile download excel file with above examples here and play with selenium VBA.

Update : Read best practices of scraping here

Also, read Parse HTML in Excel VBA – Learn by parsing hacker news home page

If you have and questions or feedback, comment below.

Author: Ranjith kumar

A CA student by education, self taught coder by passion, loves to explore new technologies and believes in learn by doing.

17 Thoughts

  1. Hi,
    HTML code:

    Select Part

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10

    Show Entries

    Total : 461 nbsp;     Processed : 461

    The photo matching process for all the entries in this part is completed

    how to scrape total andf processed values.
    Thanks

    1. Hi, I cannot tell without looking at the HTML code, But you can just use any selector for the elements with total and processed values. You can use Id, name,classname,tag or xpath as selector.

  2. Hi Ranjith,
    How can i get values from textboxes from already opened webpage in chrome.
    Could you please help me on how to do it using vba, i wanted textbox values to be copied to excel

    Regards,
    Sugat

    1. Hi Sugat,

      Unfortunately, Selenium cannot control an existing or already opened browser. You have to open the web page using selenium. In your case, next best option would be to develop a chrome extension which exports values from a web page to excel.

      1. any idea if we can do it offline like using “MSXML2.ServerXMLHTTP”…. i am begginer of VBA but just lookign if any solution. Currently user are inputing values in textboxes in a webpage and same data they have to copy in a excel sheet. So any better way to achive it

        1. Is the web page owned by you? If yes then you might be storing the data in a database which can be pulled from excel using XMLHTTP. I doubt if there is any other option. Do you have a link to the web page?

          1. No webpage is not owned by me. my team work do some processing activity in that. So they are currently openign the site in chrome and follow certain process to navigate to particular page. So we need a code so that data should be copied from already opened webpage to excel

          2. In that case, Building a custom browser extension according to that webpage is the only option available. Feel free to start a discussion on our forum with further details if you need any other help.

  3. Could you pls explain how you figured out the items in the line postdata = “{‘SiteId’:2,’CampaignId’:0,’CategoryId’:1862,’StartIndex’:0,’NumberToGet’:239,’SortId’:5,’IsLoadMore’:true}” ?

    1. Hi Rajesh, The latest version of Selenium wrapper should work with Microsoft object library 16.0
      What error are you getting?

  4. Hi Ranjith,

    Could you pls explain that how can we do switch window from browser page to download pop up and focus goes back to browser.. we tried many options but No luck.. can you pls..

    Thanks in advance!..:)

    1. Hi,
      Did you mean the SaveAs dialog? There’s no way to control that using selenium. But you can use timeouts and keystrokes as a workaround.

  5. Hi I am trying to get some help in MoveToElement. Since the elements are not visible and giving as IsDisplayed=”False”. I have successfully found the xPath but and it gives me IsPresent in Watch Window, but MoveToElement is not working as it is giving me syntax error. Would request you to help me with this.

    Declaration
    Dim element as WebElement, actions as Selenium actions

    Code is
    WebElement abc = element.FindElementByXpath(“//CorrectPath”)
    actions.MoveToElement(abc)

  6. Hi..

    I am trying to open a secure webpage in IE explorer. I am getting runtime overflow error -6.

    Dim IE As New IEDriver

    IE.Start “iexplore”, “https://ccbcat1.in.telstra.com.au/ccopenui_enu/start.swe?SWECmd=Login”
    IE.SetCapability CapabilityType.ACCEPT_SSL_CERTS, True

    IE.Get “/”

    Can you please help if this issue with IE?

    Thanks

Leave a Reply

Your email address will not be published. Required fields are marked *