[ 一日30分 인생승리의 학습법] VBA Web Scraping: How Can VBA Be Used To Scrape Website Data?

According to research by Zipdo, more than 1.2 billion people worldwide use Microsoft Office. If you’re part of this group, you can also use the VBA programming language and do VBA web scraping.

Table of Contents

 

Never heard of VBA or web scraping? Not sure how they could benefit you and your organization? You’re in the right place.

This in-depth guide explains all that you need to know about VBA programming, VBA web scraping, and everything in between.

What Is VBA Programming?

VBA stands for Visual Basic for Applications. It is a programming language developed by Microsoft that is primarily used for automating tasks within Microsoft Office applications such as Excel, Word, Access, and Outlook.

VBA is an object-oriented programming language. It involves working with objects, properties, methods, and events. In the context of Office applications, objects might include worksheets, cells, ranges, and charts.

What Is VBA Programming Used For?

VBA allows users to write code to automate repetitive and time-consuming tasks, create custom forms and user interfaces, interact with other Office applications, and manipulate data.

VBA also includes a feature called macro recording, which allows users to record a series of actions in an Office application and automatically generate VBA code that reproduces those actions.

This programming language allows users to create custom forms and user interfaces within Office applications, providing a way to interact with users in a more controlled and specific manner. It supports event-driven programming, where specific actions or events trigger the execution of code, as well. For example, a button click or a cell value change can trigger a specific subroutine.

What Is a VBA Module?

Another important part of understanding VBA is understanding VBA modules.

In VBA, a module is a container meant for storing and organizing code. It is a space where you can write and store VBA procedures, functions, and other types of code. Modules allow you to organize your code logically and make it more manageable.

There are two main types of modules in VBA:

• Standard Modules: These are general-purpose modules where you can write standalone procedures and functions. Standard modules are not associated with any particular object or form. You can create multiple standard modules in a VBA project, and they typically contain reusable code that can be called from various parts of your application.
• Class Modules: What is class module in VBA? Class modules are used in object-oriented programming in VBA. They allow you to define custom objects with properties, methods, and events. Each instance of a class module represents a specific object with its own set of characteristics and behaviors.

VBA Programming and Web Scraping

You can utilize VBA for several purposes, including web scraping.

Web scraping is a technique used to extract data from websites. It involves fetching the HTML content of a web page and then parsing and extracting specific information from that content. Web scraping is commonly used for various purposes, such as data extraction, content aggregation, price monitoring, and more.

Many organizations use a web scraping API (Application Programming Interface) to make the scraping process easier. A web scraping API is a service or tool that provides a structured way for developers to extract data from websites without directly accessing the HTML source code.

Unlike traditional web scraping, where developers write custom scripts to fetch and parse HTML content, a web scraping API simplifies the process by offering a set of endpoints that allow users to request specific data from the target website.

How does VBA programming fit into the equation?

The VBA programming language can be used for web scraping by leveraging its ability to make HTTP requests and parse HTML content. VBA can interact with websites, retrieve HTML data, and extract information programmatically.

Why would you want to use VBA for web scraping and scrape data from websites into Excel (or other Microsoft tools)?

First of all, web scraping saves you a lot of time and allows you to be more productive. Instead of copying and pasting data manually, you can relax and let an automated tool do the work for you. Using CBA for web scraping can also help you reduce the number of errors you make when transferring data from one place to another.

Benefits of Using VBA Code for Web Scraping

People use VBA web scraping for many reasons. Here are some of the top advantages that come with utilizing this programming language:

• Ready to use: VBA is bundled with Microsoft Office. If you already have Microsoft Office installed, you don’t have to worry about any additional installations. You can use VBA right away in any Microsoft Office tool.
• Reliability: Both Microsoft Excel and VBA are developed and maintained by Microsoft. That means you can upgrade these tools together to ensure you’re always working with the latest version.
• Out-of-the-box support: When you use VBA web scraping, you can take advantage of Microsoft’s browser, Microsoft Edge, which makes it easier to scrape dynamic websites.
• Complete automation: When you run the VBA script, you don’t have to carry out any additional tasks or interact with the browser at all. The VBA script takes care of everything, even logins, scrolling, and button clicking.

Of course, there are a few downsides to using VBA to scrape website data. For example, because it’s so tightly connected to Microsoft Office tools, it can be difficult to use it with other platforms. It also works best in a Windows environment, making it less accessible to Mac users, and is harder to learn than other programming languages like Python and JavaScript.

How to Do Excel VBA Web Scraping in Chrome

Excel VBA web scraping can be done in Chrome (one of the world’s most popular browsers with 2.65 million users) using a few different methods, including using the Selenium library for VBA (this is one of the most popular options).

If you’ve never done this before, don’t worry. Here’s a step-by-step guide on how to perform web scraping using Excel VBA in Chrome:

Step 1: Download the Selenium library

First, you should make sure you have the Selenium library installed since you’ll reference it in your VBA project. You can download the Selenium-type library (WebDriver) from the SeleniumHQ website: https://www.selenium.dev/.

Step 2: Set up your Excel workbook

Next, Open Excel and create a new workbook. Then, Press Alt + F11 to open the Visual Basic for Applications (VBA) editor.

Step 3: Set references

In the VBA editor, go to Tools > References.

Look for “Selenium Type Library” or “WebDriver” and check the box to enable it. Click “OK” to close the references window.

Step 4: Insert a module

Right-click on the project explorer in the VBA editor. Then, choose Insert > Module to add a new module to your project.

Step 5: Write the VBA code

In the module, you can write VBA code to control Chrome using Selenium. Here’s a basic example:

vba

Copy code

‘ Declare variables

Dim driver As New Selenium.ChromeDriver

Dim elem As Selenium.WebElement

‘ Open Chrome browser

driver.Start “chrome”, “https://example.com”

‘ Find and interact with elements

Set elem = driver.FindElementByXPath(“//input[@id=’search’]”)

elem.SendKeys “web scraping”

‘ Perform other actions as needed

‘ …

‘ Close the browser

driver.Quit

Step 6: Run the code

Press F5 or click the “Run” button to execute the code.

From here, you’ll see Chrome opening and navigating to the specified URL, performing actions, and then closing.

Step 7: Adjust code for your specific web scraping scenario

You can always Modify the code to suit your specific web scraping needs. Identify the HTML elements you want to interact with and adjust the code accordingly.

Can You Do VBA Web Scraping in Chrome without Selenium?

It’s not easy to do VBA web scraping in Chrome without Selenium. However, if you have a reliable web scraping API, you don’t have to worry about keeping track of all the code required.

That’s where a tool like Scraping Robot comes in handy.

Scraping Robot is a system made to assist developers and help users like you work more efficiently. It’s easy to use and is up and running within minutes, allowing you to scrape websites on Chrome, Microsoft Edge, and other browsers quickly and accurately.

Final Thoughts

VBA programming is an excellent option for scraping website data, especially if you’re a Windows user who already relies heavily on Microsoft Office tools like Excel.

One of the primary downsides to using VBA web scraping, though, is that VBA comes with a steeper learning curve than some other programming languages. Luckily, there are tools that can help you overcome that learning curve.

With the right tools, such as the Scraping Robot API, you can overcome these hurdles and enhance your web scraping process.

 

[출처] https://scrapingrobot.com/blog/vba-web-scraping/