Skip to content

VBA Web Scraping in Excel: Build a Reliable Scraper

Excel is often where the data needs to end up, so it makes sense to keep the scraper there too. This guide shows how to build a VBA web scraper that sends an HTTP request, receives structured JSON, and writes the result into worksheet cells. It also explains why the old Internet Explorer automation path no longer works and how to decide when VBA is the right host for the job.

Why VBA still fits some scraping jobs

VBA is Microsoft's built-in automation language for Office applications. In Excel it runs inside a VBA project, can read and write ranges, and can make network calls. That combination is useful when the workflow starts and ends in a spreadsheet: a colleague opens the workbook, clicks a button, and the data lands in the right columns.

The modern pattern is not to drive a browser from VBA. Instead, the script sends an HTTP request to a scraping service that handles rendering and JavaScript execution on its servers, then parses the returned JSON. The VBA side stays small and focused on Excel: build the request, read the response, write cells.

Use VBA when your team already works in Excel, when the output is a spreadsheet, and when non-technical colleagues need to run the job without installing extra software. If you need to crawl hundreds of pages regularly, handle complex authentication, or do heavy data processing, treat VBA as the reporting layer and move the scraping work elsewhere.

Set up Excel for a VBA scraper

The Developer tab is hidden by default. Right-click the ribbon, choose Customize the Ribbon, tick Developer in the right-hand column, and confirm. The tab then exposes Visual Basic, Macros, and References.

Open the Visual Basic Editor with Alt+F11 or the Visual Basic button. In the Project Explorer, right-click your project, choose Insert, then Module. That module is where the scraping code lives.

Under Tools > References, add Microsoft WinHTTP Services for HTTP requests. If you will parse JSON, add Microsoft Scripting Runtime and import a JSON parser. The Microsoft HTML Object Library is only needed if you still parse the DOM directly; the API-first approach rarely needs it.

The JSON parser used in the supplied example is the free JsonConverter.bas module from VBA-JSON. Download the .bas file, then use File > Import File in the editor. That gives you JsonConverter.ParseJson for turning the response text into objects you can index.

A working example: quotes into two columns

The supplied example targets https://quotes.toscrape.com/ and asks the API for a list of quote objects. The request URL carries three pieces: the API key, the encoded target URL, and an extract_rules value that describes the selector and the fields to return.

In plain terms, the extract_rules payload says: find elements matching .quote, treat them as a list, and for each item return the text of .text as text and the text of .author as author. The response is JSON shaped like a quotes array, and each entry has text and author keys.

The VBA side then writes a header row (Quote, Author) and loops from zero to the count of quotes minus one, writing each item into columns A and B starting at row two. The loop bound is the array count, so an empty result writes only the header.

A field map for the example:

  • api_key: your credential string, supplied as YOUR_API_KEY in the example.
  • url: the target page, passed through WorksheetFunction.EncodeURL.
  • extract_rules: the selector and output field description.
  • quotes[].text: the quote body, written to column A.
  • quotes[].author: the author name, written to column B.

Replace the target URL and the extract_rules selectors to reuse the same skeleton for another list-shaped page. Keep the header write and the loop bound together so a changed array name does not leave stale rows behind.

Write results to cells and named ranges

For a single value, Cells(row, column).Value writes to a specific cell, and Range("ProductName").Value writes to a named range. That gives precise control over where each field lands.

For arrays, a For loop over the item count writes one row per item. The supplied product example writes name, price, and description into columns one, two, and three, starting at row two. The same pattern works for any list of items.

Because the API does the browsing, there is no local browser object to close. That removes a common source of memory leaks and hung processes from the old IE-based approach. There is also no ReadyState loop to babysit, so the script does not hang waiting for a page that never finishes loading.

The old Internet Explorer path and why it fails

Older VBA scrapers created an InternetExplorer.Application object, set it visible, called IE.navigate, waited in a Do While loop until ReadyState was 4 and Busy was false, then read elements with getElementsByClassName. That sequence depended on a browser Microsoft fully deprecated in 2022, and on page structures that change without notice.

Swapping in Microsoft Edge does not remove the underlying problem: you still manage drivers, timing, and dynamic pages. Modern sites often use JavaScript frameworks that IE cannot run, and some block IE user agents outright.

The distinction that matters: IE automation is broken, but VBA itself is not. A VBA script that calls an HTTP API and parses JSON avoids the browser entirely, so it does not inherit the deprecation problem. If your existing macro uses CreateObject("InternetExplorer.Application"), that is the part to replace, not the whole workbook.

SeleniumBasic and cross-platform limits

SeleniumBasic is a VBA library that automates Chrome or Firefox instead of IE. It works, but it brings browser drivers that must be installed and kept current, more complex code with timing issues, and breakage when browsers update.

VBA is also primarily Windows-focused. Excel for Mac supports VBA, but some web-related objects are unavailable or behave differently on macOS. If your team is mixed-platform, plan for that before standardizing on a VBA scraper.

When scale or complexity grows, Python or a dedicated API is the better scraping layer. Excel can still be the destination: Python can write .xlsx files, and an API can deliver structured data that VBA writes into the sheet. The supplied API supports CSS and XPath selectors and an AI-assisted extraction mode for harder pages.

Decision checklist before you build

Use this checklist to decide whether the VBA route is appropriate.

  • The output must live in an Excel workbook and the workflow starts there.
  • The people running the job are comfortable in Office and should not install extra tools.
  • The target pages return list-shaped data that a selector can describe.
  • You can accept an external HTTP call from the workbook.
  • You have a plan for the API credential, such as a workbook-level setting rather than a hard-coded string.

If any of these fail, consider Power Query (Get & Transform) for simpler web tables, or Python with Pandas for larger jobs. Both can still write into Excel, so the reporting layer does not have to change.

Frequently asked questions

Is VBA still usable for web scraping?

Yes, for Excel-centric workflows. The reliable pattern is to let VBA send an HTTP request to a scraping service and parse the JSON response. Direct browser automation through Internet Explorer is obsolete, but the API approach keeps VBA focused on reading and writing spreadsheet data.

What do I need to enable before writing the scraper?

Enable the Developer tab, open the Visual Basic Editor, insert a module, and add the WinHTTP reference. For JSON parsing, add Microsoft Scripting Runtime and import a JSON parser such as the JsonConverter.bas module from VBA-JSON. The HTML Object Library is only needed if you parse the DOM directly.

Why does my old IE-based macro hang or return nothing?

Internet Explorer was fully deprecated in 2022, and many sites now refuse IE user agents or rely on JavaScript frameworks IE cannot run. A Do While loop waiting on ReadyState can also hang indefinitely on complex pages. Replace the browser object with an HTTP request to an API and parse the returned JSON instead.

How do I write a list of results into rows?

Use Cells(row, column).Value for single values and Range("NamedRange").Value for named ranges. For a list, loop from zero to the item count minus one and write each field into a new row, as the supplied product example does for name, price, and description. Format date and number columns explicitly.

What are the alternatives if VBA becomes too limited?

Power Query (Get & Transform) has better built-in web connectivity for simple tables. Python with Pandas can scrape and write .xlsx files for larger jobs. Specialized add-ins can also connect Excel to web data. In each case Excel can remain the destination for reporting.