Selenium is widely recognized not only as a powerful tool for automation testing but also for data scraping allowing users to extract information from HTML web pages. By simulating human interaction with web browsers, Selenium enables automated navigation through pages, input handling, and element selection to retrieve data directly from web elements.
This capability is particularly useful for data scraping tasks, where information such as text, images, URLs, or table data needs to be gathered systematically from multiple pages. Selenium’s flexibility across various browsers and programming languages makes it an ideal choice for those needing precise control over web interactions to extract structured data effectively.
Steps to prepare Excel Macro
Step 1: Open an Excel-based Macro.
Step 2: Insert a new module.
Step 3: Initialize a new subroutine and name it.
Step 4: Under Tools > References. Add Selenium Type Library. These libraries help in opening google Chrome and developing a macro script.
Now the excel file is ready to interact with the browser.
How to open Google Chrome using VBA?
Step 1: Declare and initialize the variable in the subroutine.
Sub test()
Dim driver As New WebDriver
Dim rc, cc, colC As Integer
Step 2: For opening Google Chrome using VBA and Selenium, you need to write command a driver.start ”chrome” and press F5.
Sub test()
Dim driver As New WebDriver
Dim rc, cc, colC As Integer
driver.Start “Chrome”
Application.Wait Now + TimeValue(“00:00:20”)
End Sub
How to open a Website in Chrome Browser using VBA?
Now we need to pass the URL to access the website on the browser.
Sub test()
Dim driver As New WebDriver
Dim rc, cc, colC As Integer
driver.Start “Chrome”
driver.get “https://www.google.com”
Application.Wait Now + TimeValue(“00:00:20”)
End Sub
How to scrape information from the website using VBA?
If someone needs daily data scraping from a website, they can automate it so that a single click pulls updated data directly into Excel. Using Selenium with Excel VBA, a script can be set up to connect, navigate, and extract the latest data instantly. This saves time and ensures accurate, real-time information, ideal for tasks like tracking prices or monitoring sales.
The first step is to Inspect Element and check how the data is structured in the source content of the website.
Let us assume that the source code looks like this:
<table class=”datatable”>
<thead>
<tr>
<th>Company</th>
<th>Group</th>
<th>Pre Close (Rs)</th>
<th>Current Price (Rs)</th>
<th>% Change</th> </tr>
As it is clear that data is structured in the single HTML table. So we would design a macro which will pull the header information and corresponding data from the HTML table.
Step 1: Create a for loop that runs as a collection using FindElementByTask() and FindElementByTag()Sub test() Dim driver As New WebDriver Dim rowc, cc, columnC As Integer rowc = 2 Application.ScreenUpdating = False driver.Start “chrome” driver.get “http://demo.guru99.com/test/web-table-element.php” For Each th In driver.FindElementByClass(“dataTable”).FindElementByTag(“thead”).FindElementsByTag(“tr”) cc = 1 For Each t In th.FindElementsByTag(“th”) Sheet2.Cells(1, cc).Value = t.Text cc = cc + 1 Next t Next th End Sub
Step 2: Selenium will now locate the data using the above similar approach.Sub test() Dim driver As New WebDriver Dim rowc, cc, columnC As Integer rowc = 2 Application.ScreenUpdating = False driver.Start “chrome” driver.get “http://demo.guru99.com/test/web-table-element.php” For Each th In driver.FindElementByClass(“dataTable”).FindElementByTag(“thead”).FindElementsByTag(“tr”) cc = 1 For Each t In th.FindElementsByTag(“th”) Sheet2.Cells(1, cc).Value = t.Text cc = cc + 1 Next t Next th For Each tr In driver.FindElementByClass(“dataTable”).FindElementByTag(“tbody”).FindElementsByTag(“tr”) columnC = 1 For Each td In tr.FindElementsByTag(“td”) Sheet2.Cells(rowc, columnC).Value = td.Text columnC = columnC + 1 Next td rowc = rowc + 1 Next tr Application.Wait Now + TimeValue(“00:00:20”) End Sub
Step 3: Once the macro is ready, pass, and assign the subroutine to excel button and exit the module. Label the button as any name.
Step 4: Press the labeled button to get the desired output.
Step 5: Compare the scraped results of Excel file with the results of the Chrome Site.
Conclusion
In conclusion, data scraping using Excel VBA and Selenium offers a powerful solution for automating the extraction of information from websites directly into spreadsheets. This combination brings together the flexibility of Selenium’s web automation with the familiarity and functionality of Excel, enabling users to manage, analyze, and store data efficiently.
Whether you’re gathering product details, tracking financial data, or conducting research, integrating VBA and Selenium streamlines the entire process, saving time and reducing manual work. By mastering these tools, you can create robust, reusable scripts that transform how you handle data. Embrace this approach to enhance productivity and gain valuable insights effortlessly!
Call to Action
Need a faster way to extract data from websites into Excel? “Data Scraping using Excel VBA and Selenium” is the solution! By combining Selenium’s web automation with Excel’s VBA scripting, you can automate data extraction and populate spreadsheets with critical information in seconds. This approach is perfect for tasks like market research, financial tracking, and e-commerce analysis, making data collection more efficient and accurate.
At H2K Infosys, our expert-led training will teach you how to integrate Selenium with Excel VBA, build reusable scripts, and handle real-world data extraction challenges. With hands-on guidance, you’ll gain the skills to automate complex scraping tasks, boost productivity, and transform how you manage data. Enroll today and elevate your automation expertise with H2K Infosys!
One Response