🚀The world's best VBA AI has evolved. ExcelMaster is now an autonomous Agent.Read more →
Back to Blog

VBA Web Scraping in Excel — Get Data from a Website Without Internet Explorer

|

VBA Web Scraping in Excel — Get Data from a Website Without Internet Explorer

TL;DR — A scraping macro receives the HTML the server sent, not the page you see. Your browser runs JavaScript after the page arrives and often builds the table you want at that point, so it never appears in what VBA downloads. Check first: open View Page Source (Ctrl+U) and search for a value you need. If it is there, download the page with XMLHTTP and read it with an htmlfile document. If it is not, the page loads its data from a JSON request, and calling that request directly is easier and more stable than any scraping. Internet Explorer automation is no longer an option.

Dim http As Object, doc As Object
Set http = CreateObject("MSXML2.XMLHTTP.6.0")
http.Open "GET", "https://example.com/prices", False
http.send
Set doc = CreateObject("htmlfile")
doc.body.innerHTML = http.responseText
Debug.Print doc.getElementsByTagName("table").Length & " tables found"

This is the third article in a cluster on getting data from the web into Excel: VBA HTTP request covers the request, VBA JSON the structured response, and this one pages that were written for people rather than code. The cluster idea is that a web request hands you text, not data. Between the server and your cells sit three translations you own: bytes to text, text to structure, structure to grid. Scraping is the version where the structure was never meant for you.

What you'll learn

  • The mental model: the HTML the server sent
  • The ten-second test: View Page Source
  • When the data is not in the source: find the JSON
  • Why not Internet Explorer or Selenium
  • Reading an HTML table with htmlfile
  • Scraped numbers are text
  • Garbled text from a meta charset
  • Power Query and polite scraping

The mental model: the HTML the server sent

A browser does two things with a page. It downloads the HTML, then it runs the page's JavaScript, which often fetches more data and builds tables, prices and lists on the screen. What you see is the result of both steps. What VBA downloads with an HTTP request is only the first step: the HTML as the server sent it.

That is why the most common scraping complaint is "my code finds no table, but I can see the table". Both statements are true. The table exists in the browser's live page and not in the HTML the server sent. No selector, wait or retry fixes that, because the data was never in what you downloaded.

The ten-second test: View Page Source

Before you write any code, open the page in a browser and press Ctrl+U (View Page Source). This shows the HTML as the server sent it, the same thing VBA will receive. Search it (Ctrl+F) for a value you can see on the page, such as a price or a name.

  • Found: the data is in the server's HTML. Scrape it with XMLHTTP and htmlfile, below.
  • Not found: JavaScript loads it. Go to the next section.

Do not use Inspect (F12, Elements) for this test. Inspect shows the live page after JavaScript has run, so it shows the data in both cases.

When the data is not in the source: find the JSON

A page that builds its table with JavaScript has to get the data from somewhere, almost always a JSON request to the same site. Find it:

  1. Open the browser's developer tools (F12) and go to the Network tab.
  2. Filter by Fetch/XHR and reload the page.
  3. Click the requests and look at their Response. One of them contains your data as JSON.
  4. Copy its URL and call it from VBA as shown in VBA HTTP request, then parse it with VBA JSON.

This route is better than scraping even when scraping would work. The JSON has field names instead of table positions, numbers without currency symbols, and it changes less often than the page design. Check the site's terms before you build on it; an undocumented endpoint can also change without notice.

Why not Internet Explorer or Selenium

Older tutorials drive a browser from VBA with CreateObject("InternetExplorer.Application"). Internet Explorer was retired in June 2022; it is not supported, and modern websites increasingly do not work in it. Do not start new work on it, and plan to replace macros that depend on it.

Selenium Basic, the usual VBA replacement, has not had a release since 2016 and needs a browser driver that matches the installed Chrome or Edge version; every browser update can break the macro on the user's machine. It can still be the right tool for a page that only works with JavaScript and has no data request you can call, but treat that as a last resort, not a default.

Reading an HTML table with htmlfile

For data that is in the server's HTML, you need no browser. Download the page and load it into an htmlfile object, which gives you a document you can search like a web page:

Sub ScrapeFirstTable(ByVal url As String, ByVal target As Range)
    Dim http As Object, doc As Object, tbl As Object
    Dim out() As Variant, r As Long, c As Long, nCols As Long

    Set http = CreateObject("MSXML2.XMLHTTP.6.0")
    http.Open "GET", url, False
    http.setRequestHeader "User-Agent", "Mozilla/5.0"
    http.send
    If http.Status <> 200 Then Err.Raise vbObjectError + 514, , "HTTP " & http.Status

    Set doc = CreateObject("htmlfile")
    doc.body.innerHTML = http.responseText
    If doc.getElementsByTagName("table").Length = 0 Then Err.Raise vbObjectError + 515, , "No table"
    Set tbl = doc.getElementsByTagName("table")(0)

    nCols = 0
    For r = 0 To tbl.Rows.Length - 1
        If tbl.Rows(r).Cells.Length > nCols Then nCols = tbl.Rows(r).Cells.Length
    Next r
    ReDim out(1 To tbl.Rows.Length, 1 To nCols)
    For r = 0 To tbl.Rows.Length - 1
        For c = 0 To tbl.Rows(r).Cells.Length - 1
            out(r + 1, c + 1) = Trim$(tbl.Rows(r).Cells(c).innerText)
        Next c
    Next r
    target.Resize(UBound(out, 1), nCols).Value = out
End Sub

Three details matter. The collections from the HTML document (Rows, Cells, getElementsByTagName) start at 0, unlike most of Excel. Rows can have different numbers of cells, so size the array by the widest row. And the whole table is written to the sheet in one step.

To find a specific element, rely on getElementById and getElementsByTagName. Depending on the Windows and Office build, a late-bound htmlfile document can run in an old compatibility mode where newer methods such as querySelector and getElementsByClassName are missing, so code built on them works on one machine and not on another. To filter by class, loop over tags and test .className.

Selecting "the first table" is the weakest point of any scraper. A site redesign adds a table above yours and the macro silently reads the wrong one. If the table has an id, use it; otherwise check a header cell's text before you trust the table.

Scraped numbers are text

Everything you take from a page with innerText is a string, formatted for the people who read that site: 1.234,56 € on a German site, 1 234,56 € on a French one, $1,234.56 on an American one. Written to a cell as text, it does not add up. Converted with CDbl or Val, it gives a wrong number or an error, depending on the user's regional settings.

Convert using the site's format, not the user's. Strip the currency and the thousands separator, then make the decimal separator a point and use Val:

Function ParseScraped(ByVal s As String, ByVal thousandsSep As String, ByVal decimalSep As String) As Variant
    s = Replace(s, ChrW(160), "")          ' non-breaking space
    s = Replace(s, ChrW(8239), "")         ' narrow non-breaking space
    s = Replace(s, " ", "")
    s = Replace(s, ChrW(8364), ""): s = Replace(s, "$", "")   ' euro sign
    If thousandsSep <> "" Then s = Replace(s, thousandsSep, "")
    s = Replace(s, decimalSep, ".")
    If s Like "*[!0-9.-]*" Or s = "" Then ParseScraped = CVErr(xlErrValue) Else ParseScraped = Val(s)
End Function

v = ParseScraped(cellText, ".", ",")   ' "1.234,56" from a German site -> 1234.56

The two non-breaking spaces are the hidden trap. French and other European sites often separate thousands with them, they look exactly like a space, and Trim does not remove them. See VBA Trim for the same character in pasted data.

Garbled text from a meta charset

responseText decodes the page using the character set in the server's HTTP header. Many older sites declare their encoding only inside the HTML, with a <meta charset> tag, and XMLHTTP does not read that tag. The page looks fine in the browser and arrives in VBA with é instead of é, or with boxes instead of Japanese text.

If the source has a <meta charset="windows-1252"> or <meta charset="shift_jis"> tag and the text is garbled, decode http.responseBody with that character set, using the ADODB.Stream function from VBA HTTP request, and pass the result to doc.body.innerHTML.

Power Query and polite scraping

For a static HTML table that you want to refresh, Excel already has a scraper: Data > Get Data > From Web (Power Query) lists the tables on a page, loads the one you pick and refreshes it with a click, without VBA. It has the same limit, since it also reads the server's HTML, but when it works it is less code to maintain. Use VBA when you need logic: a list of URLs, a login, or combining pages.

And scrape politely. One request per page, a pause between pages (see VBA Wait), no re-downloading what you already have, and a look at the site's terms and robots.txt first. A macro that fires a hundred requests a second gets your office's IP address blocked, and then nobody's macro works.

The judgment call: scraping is a contract nobody signed

An API is a promise: the provider documents it and tries not to break it. A web page makes no promise. The designer can add a table, rename a class or move prices into JavaScript tomorrow, and your macro breaks without an error, or worse, reads the wrong column.

So choose the most stable source available: an official API first, then the JSON request behind the page, then Power Query on a static table, then XMLHTTP with htmlfile, and browser automation last. Whatever you choose, make the scraper check what it found (a header text, a row count) before it writes, so that a redesign produces an error instead of wrong numbers.

How ExcelMaster helps

Scrapers break quietly: the layout changes, the numbers come back formatted for another country, the text arrives garbled.

ExcelMaster checks what the server actually returns before it writes the code, looks for the data request behind JavaScript pages, and converts scraped numbers using the site's format so they arrive in your sheet as real numbers.

Frequently asked questions

How do I scrape a website into Excel with VBA?

Download the page with MSXML2.XMLHTTP.6.0, load responseText into CreateObject("htmlfile"), find the table with getElementsByTagName("table"), read each cell's innerText into an array and write the array to the sheet.

Why does my VBA scraper not find data that I can see on the page?

The page builds that data with JavaScript after it loads, so it is not in the HTML that VBA downloads. Find the JSON request in the browser's Network tab and call it directly.

Can I still use Internet Explorer for web scraping in VBA?

It is not recommended. Internet Explorer was retired in June 2022 and is not supported. Use XMLHTTP with htmlfile for static pages and the page's JSON request for dynamic ones.

Why are scraped numbers not numbers in Excel?

innerText always returns text, formatted for the site's country. Remove currency symbols and thousands separators, including non-breaking spaces, change the decimal separator to a point and convert with Val.

Is Power Query better than VBA for web scraping?

For a static table you want to refresh, yes: Data > From Web needs no code. Use VBA when you need to loop over many pages, log in, or apply logic between requests.

Tested in

Tested in: Excel 365 (Windows 11), VBA 7.1 — last verified 2026-10-05.

Related guides: VBA HTTP Request · VBA JSON · VBA Trim · VBA Wait · VBA 2D Array · VBA Replace · VBA Like · VBA CreateObject