How to scrape with Excel?

Did you know it was possible to make web scraping with Excel? Thanks to Power Query and VBA, You can import, structure and update data without the need for complex tools.

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

✅ Power Query is an Excel-integrated tool. It is used to import, transform and analyze data. With it, you can :

• 🔥 Retrieve data from the web, a file, a database, etc.
• 🔥 Clean or transform this data.
• 🔥 Load data automatically.

This is the easiest way to scrape with Excel. Find out how.

Step 1: Access the import tool

In Excel, go to Données > Getting data > From the web.

Step 2: Paste the web page URL

Paste the address of the site or page containing the data you wish to extract.

Step 3: Navigate the “Browser”

A window opens with the detected tables. Select the relevant table or data. Example: an HTML table.

Step 4: Load data into an Excel sheet

Click on Load to import data directly into your Excel sheet.

💡 To retrieve the latest information from the website, refresh the data. Right-click in your table to >. Update.

How to scrape with VBA and Excel macros?

✅ VBA Where Visual Basic for Applications is a programming language integrated into Excel and other Microsoft Office programs. It is used to :

• 🔥 Automate repetitive tasks.
• 🔥 Create macros, i.e. small programs that run in Excel.
• 🔥 Open a web page, retrieve text or tables and paste them directly into Excel.

Although more complex, VBA offers advanced control : page navigation, login with authentication, dynamic data extraction, etc.

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

Sub ScraperTable()

    ' Object declaration
    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 be scrapped
    Dim url As String
    url = "https://www.w3schools.com/html/html_tables.asp"
    
    ' Create HTTP object
    Set http = CreateObject("MSXML2.XMLHTTP")
    http.Open "GET", url, False
    http.Send

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

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

    ' Loop over rows and columns
    For i = 0 To table.Rows.Length - 1
        For j = 0 To tableau.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 Create objects to manage web requests and HTML content.
• url = "..." URL of the site to be scrapped.
• http.Open "GET", url, False Sends an HTTP request to load the page.
• html.body.innerHTML = http.responseText The HTML response is inserted into a manipulatable object.
• Set table = html.getElementsByTagName("table")(0) : We target the FIRST PANEL from page
• Double loop For i ... For j ... We read each cell in the table and paste it into Excel (corresponding cell).

👉 VBA use cases for scraping

• Pages requiring authentication (login/password).
• Dynamic pagess where Power Query does not work.
• Retrieve multiple tables on the same page or navigate between several pages.
• Automation Daily data update: schedule daily data updates with a button or trigger.

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

The choice between Power Query and VBA will depend on your needs. Here's a quick comparison to help you choose the most suitable method.

➡ Basically, Power Query is perfect for beginners and most basic tasks, while VBA is for advanced users with specific needs.

What are the alternatives to Excel for web scraping?

Excel is handy, but limited. For more ambitious projects, there are powerful alternatives.

No-code/Low-code tools

They can be used to extract web data without writing code. We quote:

• Bright Data a professional, powerful and scalable solution.
• Octoparse Easy to use, with a simple visual interface, perfect for beginners.

Programming languages

They enable scraper websites in a highly flexible way. You can browse multiple pages automatically, manage sites with authentication, and much more.

If you're looking for a powerful solution, ideal for complex projects, choose the web scraping with Python.

👉 Let's take a look at which solutions surpass Excel in power, flexibility and simplicity.

What tools and resources can help you scrape with Excel?

If you want to take scraping with Excel a step further, you can combine it with other tools such as the libraries and the game's browser extensions.

For instance :

• you can use a web scraping extension on your browser, such as Web Scraper for Chrome or Firefox
• or opt for web scraping on Python with BeautifulSoup.

Is web scraping legal?

the web scraping is legal under certain conditions. It all depends on compliance with the site's T&Cs, intellectual property and data usage.

💬 Finally, scrapping with Excel is possible and accessible, especially with Power Query. For more advanced needs, VBA offers advanced control, but other tools such as . Bright Data Where Python are sometimes more suitable.