{"id":15659,"date":"2024-03-21T11:14:27","date_gmt":"2024-03-21T05:44:27","guid":{"rendered":"https:\/\/www.h2kinfosys.com\/blog\/?p=15659"},"modified":"2025-06-16T04:33:32","modified_gmt":"2025-06-16T08:33:32","slug":"data-scraping-using-excel-vba-and-selenium","status":"publish","type":"post","link":"https:\/\/www.h2kinfosys.com\/blog\/data-scraping-using-excel-vba-and-selenium\/","title":{"rendered":"Data Scraping using Excel VBA and Selenium"},"content":{"rendered":"\n

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. Many Selenium certification<\/a> courses cover Selenium\u2019s capabilities in depth, equipping learners with the skills to use it effectively for both testing and data extraction tasks.<\/p>\n\n\n\n

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\u2019s 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.<\/p>\n\n\n\n

Steps to prepare Excel Macro<\/strong><\/h3>\n\n\n\n

Step 1:<\/strong> Open an Excel-based Macro.<\/p>\n\n\n\n

\"Data<\/figure>\n\n\n\n

Step 2: <\/strong>Insert a new module.<\/p>\n\n\n\n

\"Data<\/figure>\n\n\n\n

Step 3: <\/strong>Initialize a new subroutine and name it.<\/p>\n\n\n\n

\"Data<\/figure>\n\n\n\n

Step 4: <\/strong>Under Tools > References. Add Selenium Type Library. These libraries help in opening google Chrome and developing a macro script.<\/p>\n\n\n\n

\"Data<\/figure>\n\n\n\n

Now the excel file is ready to interact with the browser.<\/p>\n\n\n\n

How to open Google Chrome using VBA?<\/strong><\/h3>\n\n\n\n

Step 1: <\/strong>Declare and initialize the variable in the subroutine.<\/p>\n\n\n\n

Sub test()\nDim driver As New WebDriver\nDim rc, cc, colC As Integer<\/code><\/pre>\n\n\n\n
Step 2: <\/strong>For opening Google Chrome using VBA and Selenium, you need to write command a driver.start \u201dchrome\u201d and press F5.<\/p>\n\n\n\n
Sub test()\nDim driver As New WebDriver\nDim rc, cc, colC As Integer\ndriver.Start \u201cChrome\u201d\nApplication.Wait Now + TimeValue(\u201c00:00:20\u201d)\nEnd Sub<\/code><\/pre>\n\n\n\n
How to open a Website in Chrome Browser using VBA?<\/strong><\/h3>\n\n\n\n
Now we need to pass the URL to access the website on the browser.<\/p>\n\n\n\n
Sub test()\nDim driver As New WebDriver\nDim rc, cc, colC As Integer\ndriver.Start \u201cChrome\u201d\ndriver.get \u201chttps:\/\/www.google.com\u201d\nApplication.Wait Now + TimeValue(\u201c00:00:20\u201d)\nEnd Sub<\/code><\/pre>\n\n\n\n
How to scrape information from the website using VBA?<\/strong><\/h3>\n\n\n\n
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.<\/p>\n\n\n\n
The first step is to Inspect Element and check how the data is structured in the source content of the website.<\/p>\n\n\n\n
Let us assume that the source code looks like this:<\/p>\n\n\n\n
<table class=\"datatable\">\n<thead>\n<tr> \n<th>Company<\/th> \n<th>Group<\/th>\n<th>Pre Close (Rs)<\/th>\n<th>Current Price (Rs)<\/th> \n<th>% Change<\/th> <\/tr>\n<\/code><\/pre>\n\n\n\n
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<\/a> table.<\/p>\n\n\n\n

Step 1: <\/strong>To create a for<\/code> loop that iterates through the HTML header information using Selenium, we will utilize FindElementByClass()<\/code> and FindElementByTag()<\/code> methods to locate and gather the header details of an HTML table. Here\u2019s how you could structure the code in VBA:<\/p>\n\n\n\n
Sub test2()\nDim driver As New WebDriver\nDim rowc, cc, columnC As Integer\nrowc = 2\nApplication.ScreenUpdating = False\ndriver.Start \"chrome\"\ndriver.Get \"http:\/\/demo.h2kinfosys.com\/test\/web-table-element.php\"\nFor Each th In driver.FindElementByClass(\"dataTable\").FindElementByTag(\"thead\").FindElementsByTag(\"tr\")\ncc = 1\nFor Each t In th.FindElementsByTag(\"th\")\nSheet2.Cells(1, cc).Value = t.Text\ncc = cc + 1\nNext t\nNext th<\/code><\/pre>\n\n\n\n
Step 2: <\/strong>To locate and extract table data using the Selenium driver in a similar manner, here\u2019s the revised VBA code. This script will locate the table headers and data, then write them to Sheet2 in Excel, starting from the specified cells.<\/p>\n\n\n\n
Sub test2()\nDim driver As New WebDriver\nDim rowc, cc, columnC As Integer\nrowc = 2\nApplication.ScreenUpdating = False\ndriver.Start \"chrome\"\ndriver.Get\"http:\/\/demo.h2kinfosys\/test\/web-table-element.php\"\nFor Each th In driver.FindElementByClass(\"dataTable\").FindElementByTag(\"thead\").FindElementsByTag(\"tr\")\ncc = 1\nFor Each t In th.FindElementsByTag(\"th\")\nSheet2.Cells(1, cc).Value = t.Text\ncc = cc + 1\nNext t\nNext th\nFor Each tr In driver.FindElementByClass(\"dataTable\").FindElementByTag(\"tbody\").FindElementsByTag(\"tr\")\ncolumnC = 1\nFor Each td In tr.FindElementsByTag(\"td\")\nSheet2.Cells(rowc, columnC).Value = td.Text\ncolumnC = columnC + 1\nNext td\nrowc = rowc + 1\nNext tr\nApplication.Wait Now + TimeValue(\"00:00:20\")\nEnd Sub<\/code><\/pre>\n\n\n\n
<\/text><\/path><\/svg><\/div><\/div><\/div><\/div><\/p>\n\n\n\n
Step 3: <\/strong>Once the macro is ready, pass, and assign the subroutine to excel button and exit the module. Label the button as any name.<\/p>\n\n\n\n
Step 4: <\/strong>Press the labeled button to get the desired output.<\/p>\n\n\n\n
Step 5:<\/strong> Compare the scraped results of Excel file with the results of the Chrome Site.<\/p>\n\n\n\n
\"selenium<\/a><\/figure>\n\n\n\n
Conclusion<\/strong><\/h2>\n\n\n\n
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\u2019s web automation with the familiarity and functionality of Excel, enabling users to manage, analyze, and store data efficiently. <\/p>\n\n\n\n
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!<\/p>\n\n\n\n
Call to Action<\/strong><\/h2>\n\n\n\n
Need a faster way to extract data from websites into Excel? \u201cData Scraping<\/a> using Excel VBA and Selenium\u201d is the solution! By combining Selenium\u2019s web automation with Excel\u2019s 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.<\/p>\n\n\n\n
At H2K Infosys, our expert-led Selenium training<\/a> 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!<\/p>\n","protected":false},"excerpt":{"rendered":"
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. Many Selenium certification […]<\/p>\n","protected":false},"author":1,"featured_media":15660,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[43],"tags":[],"class_list":["post-15659","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-selenium-tutorials"],"_links":{"self":[{"href":"https:\/\/www.h2kinfosys.com\/blog\/wp-json\/wp\/v2\/posts\/15659","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.h2kinfosys.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.h2kinfosys.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.h2kinfosys.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.h2kinfosys.com\/blog\/wp-json\/wp\/v2\/comments?post=15659"}],"version-history":[{"count":0,"href":"https:\/\/www.h2kinfosys.com\/blog\/wp-json\/wp\/v2\/posts\/15659\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.h2kinfosys.com\/blog\/wp-json\/wp\/v2\/media\/15660"}],"wp:attachment":[{"href":"https:\/\/www.h2kinfosys.com\/blog\/wp-json\/wp\/v2\/media?parent=15659"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.h2kinfosys.com\/blog\/wp-json\/wp\/v2\/categories?post=15659"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.h2kinfosys.com\/blog\/wp-json\/wp\/v2\/tags?post=15659"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}