How to scrape with Excel?

Excel lets you scrape web pages without installing any external tools. Power Query is the simplest method: it only takes a few clicks to extract an HTML table and import it directly into a worksheet. For more complex pages, the VBA takes over and gives you more control.

Here's how to do web scraping in both cases.

How do I scrape a web page into Excel using Power Query?

Power Query is a tool integrated into Microsoft Excel (also known as Web Query (in some versions) that is used to import, process, and analyze web data from various sources. Specifically, you can:

• Recovering web data from a website, a file, or a database.
• Clean or transform this data before analyzing it.
• Load data automatically in a spreadsheet so your team can work with up-to-date information.

This is the most simple to extract web data into Excel without writing a single line of code. Here are the four steps to follow.

Step 1: Access the import tool

In Microsoft Excel, go to the’Data tab, then click on Getting data and finally on From the web.

Step 2: Paste the URL of the web page

Paste the’URLs Select the website or page that contains the information you want to extract, then click "Submit.".

Step 3: Navigate the “Browser”

A window opens and displays the detected tables. Power Query automatically identifies the tables in the page's code. Select the table you're interested in, for example, a HTML table, to obtain a clean dataset.

Step 4: Load data into an Excel sheet

Click on Load to import the data directly into your spreadsheet. You'll quickly end up with a clean table, ready for analysis.

To get the latest information from the site, click right click in your table, then click on Update. This allows you to keep your data up to date almost in real time.

What You Need to Know Before You Get Started

Power Query works only with static HTML pages. If the table does not appear in the Browser, it is often because the page is loading its content in JavaScript : sites in React, Angular, or data that appears after scrolling.

In that case, you have two options. You can target the’API that the site calls in the background, which is often visible in the browser's developer tools. Otherwise, switch to the method VBA or a dedicated tool such as Octoparse.

How to scrape with VBA and Excel macros?

VBA (Visual Basic for Applications) is the programming language built into Microsoft Excel and other software Microsoft Office. It lets you:

• Automate repetitive tasks in Excel.
• Create macros, small programs that run directly from your file.
• Open a web page, extract text or tables, and then automatically paste them into Excel.

It's more technical than Power Query, but you get a advanced control : navigating between multiple pages, managing a connection with authentication, and collecting data that Power Query does not detect.

Here is an example of a macro that retrieves an HTML table from a page:

Sub ScraperTableau()

 ' Object declarations
    Dim http As Object, html As Object
    Dim table As Object, row As Object, cell As Object
    Dim i As Long, j As Long

 ' URL of the page to scrape
    Dim url As String
    url = "https://www.w3schools.com/html/html_tables.asp"
 
 ' Create an HTTP object
    Set http = CreateObject("MSXML2.XMLHTTP")
    http.Open "GET", url, False
    http.Send

 ' Load the HTML content
    Set html = CreateObject("HTMLFile")
    html.body.innerHTML = http.responseText

    ' Select the first table found
    Set table = html.getElementsByTagName("table")(0)

 ' Loop through the rows and columns
    For i = 0 To table.Rows.Length - 1
        For j = 0 To table.Rows(i).Cells.Length - 1
 Cells(i + 1, j + 1).Value = table.Rows(i).Cells(j).innerText
 Next j
    Next i

End Sub

Code explanation :

• Dim http As Object, html As Object : Creates the objects used to handle the web request and the HTML content.
• url = "..." : The URL of the website to be scraped.
• http.Open "GET", url, False : Sends an HTTP request to load the page.
• html.body.innerHTML = http.responseText : inserts the HTML response into a manipulable object.
• Set table = html.getElementsByTagName("table")(0) : targets the FIRST PANEL of the page.
• Double loop For i ... For j ... : Reads each cell in the array and pastes it into the corresponding cell in Excel.

Be aware of an important limitation. This code sends a simple HTTP request via MSXML2.XMLHTTP and retrieves only the static HTML returned by the server. It Cannot run JavaScript of a page. On a modern website that loads its web data dynamically, you'll get an empty result. In these cases, you need to control an actual browser, for example using Selenium and Python.

When to Use VBA for Web Scraping:

• On the pages that require authentication (username and password).
• When you have to retrieve multiple arrays on a single page or navigate between multiple pages.
• To automate Daily data collection using a button or a scheduled trigger.

Power Query vs. VBA: which method to choose for Excel scraping?

The choice between Power Query and VBA It depends on your needs and the complexity of the target site. Here's a quick comparison to help you decide.

In practice, Power Query remains the best choice for beginners and most common tasks: it takes just four steps to import web data into Microsoft Excel, without writing a single line of code.

Choose VBA only when you have a specific need that Power Query Does not cover: complex pages, authentication, advanced automation. For everything else, Power Query will save you time.

What are the alternatives to Excel for web scraping?

Excel is still handy, but it quickly reaches its limits when dealing with large volumes of web data. For more ambitious projects, other tools take over. Some of them work without code, while others require a little programming. You'll also find free tools depending on your needs.

No-code and low-code tools

These tools enable the web data collection without writing a single line of code. You configure your target using a visual interface, then export the result to the CSV format to open it directly in Excel. This is useful for the marketing intelligence or monitoring various online sources.

• Bright Data : a professional, powerful solution designed to meet the demanding needs of a business.
• Octoparse one no-code web scraper with a simple interface, perfect for beginners, and lets you export to Excel with just a few clicks.

Programming languages

A language like Python lets you scrape websites in a very flexible way. You can browse multiple pages automatically, manage sites that require authentication, and ensure data collection that would be impossible to achieve with Power Query. This is particularly useful for a company that monitors its competitors or analyzes its distribution networks.

For a complex project, check out the web scraping with Python and BeautifulSoup. This is the most powerful method when data collection becomes regular or involves large volumes of data.

Here's a quick comparison of solutions that surpass Excel in power, flexibility, and ease of use.

What tools and resources can help you scrape with Excel?

To go beyond Power Query and VBA, you can combine Excel with third-party tools. These scrapers export the data to CSV, a format that Excel opens directly. This way, you get a clean dataset, ready for analysis, without writing a single line of code.

Here are two useful options depending on your needs:

• A web scraping extension for your browser, such as Web Scraper on Chrome Where Firefox. It's the perfect tool for extracting data from a website in just a few clicks, without any complicated installation.
• the web scraping with Python. This method requires a little code, but it handles the complex pages that Power Query does not detect.

In practical terms, choose the’extension for a one-time, urgent need. Go to Python whenever you want to automate regular data collection or process a large volume of information.

Is web scraping legal?

the web scraping is legal under certain conditions. It all depends on the site’s Terms of Service, intellectual property rights, and how you use the collected data.

In practice, the public data are generally recoverable. However, as soon as you start messing with personal data (name, email, profile), enter the following in the RGPD, even if this information is publicly available on the internet.

Before scraping a website, check two simple things:

• The file robots.txt of the site, which specifies which pages crawlers are allowed or not allowed to crawl.
• The terms of use, which sometimes explicitly prohibit automated data collection.

To stay calm, limit yourself to non-personal data and follow the site's schedule so as not to overload it.