Excel VBA Web Scraping: Scrape Website Data into Excel

Web scraping is usually associated with Python or specialized services. But when the data needs to land in a spreadsheet, get crunched by formulas and shown to colleagues, Excel together with VBA is still one of the fastest ways to a result. There's no interpreter to install, no environment to configure, and no need to explain pip install to the finance team. Open the workbook, click a button, and the data is on the sheet.

In this guide we'll break down how Excel VBA web scraping works: which objects to use for HTTP requests, how to parse HTML and JSON, how to drop the result into cells, and how to avoid getting blocked. The examples are working code — you can paste them into the VBA editor and run them.

When Excel / VBA is a good choice

VBA makes sense when:

• the result lives in Excel anyway (a report, a dashboard, a quotes log);
• the data volume is small to medium — dozens to thousands of rows, not millions;
• you need one-click automation for people with no coding skills;
• the source serves data over a simple HTTP request or an open API.

If you need serious scale, evasion of heavy JavaScript defenses, or parallel requests, look toward Python instead (requests, BeautifulSoup, Playwright). VBA hits a ceiling fast on those tasks.

The tools inside VBA

VBA offers a few main "engines" for scraping:

Most of these objects are wired up on the fly through CreateObject, so they don't require manually adding references to the project. That's convenient: the workbook runs on any machine with Excel.

A basic HTTP request

The simplest scraper just fetches a page's text. Here's a function that does a GET request and returns the HTML or JSON as a string:

Function GetResponse(ByVal url As String) As String
    Dim http As Object
    Set http = CreateObject("MSXML2.XMLHTTP")

    http.Open "GET", url, False
    ' Pretend to be an ordinary browser — many sites drop requests with no User-Agent
    http.setRequestHeader "User-Agent", _
        "Mozilla/5.0 (Windows NT 10.0; Win64; x64)"
    http.send

    If http.Status = 200 Then
        GetResponse = http.responseText
    Else
        GetResponse = "ERROR: " & http.Status & " " & http.statusText
    End If

    Set http = Nothing
End Function

The third argument to Open — False — means a synchronous request: the code waits for the response. For most tasks that's enough. The User-Agent header is critical: without it, some servers return a 403 or a CAPTCHA.

Parsing JSON without libraries

VBA can't parse JSON out of the box, but for simple responses string functions are enough. Say the API returned:

{"price": 152.34, "currency": "USD", "symbol": "AAPL"}

You can extract a field's value with a small function:

Function ExtractJsonValue(ByVal json As String, ByVal key As String) As String
    Dim pattern As String
    Dim startPos As Long, endPos As Long

    pattern = """" & key & """:"
    startPos = InStr(json, pattern)
    If startPos = 0 Then Exit Function

    startPos = startPos + Len(pattern)
    ' Skip the opening quote if the value is a string
    If Mid(json, startPos, 1) = """" Then startPos = startPos + 1

    ' The value ends at a comma, a closing brace, or a quote
    endPos = startPos
    Do While endPos <= Len(json)
        Dim ch As String
        ch = Mid(json, endPos, 1)
        If ch = "," Or ch = "}" Or ch = """" Then Exit Do
        endPos = endPos + 1
    Loop

    ExtractJsonValue = Trim(Mid(json, startPos, endPos - startPos))
End Function

This approach works for flat objects. If the structure is nested and complex, pull in a ready-made JSON parser for VBA (for example, the open-source VBA-JSON module by Tim Hall) — it turns the response into a Dictionary and Collection, which are far easier to work with.

The chain "HTTP request → parse JSON → write to a cell" is the core template behind scraping currency exchange rates. Most central-bank services and FX APIs return exactly JSON or XML, and the extraction function above covers 80% of cases. A full solution with auto-refresh on a timer is in the scraping currency exchange rates guide.

Parsing HTML with MSHTML

When the data isn't in an API but sits right in the page markup, the HTMLDocument object is handy. It lets you find elements the same way a browser does — by id, tag and class.

Function ParseHtmlElement(ByVal url As String, ByVal elementId As String) As String
    Dim http As Object, htmlDoc As Object
    Set http = CreateObject("MSXML2.XMLHTTP")

    http.Open "GET", url, False
    http.setRequestHeader "User-Agent", "Mozilla/5.0"
    http.send

    Set htmlDoc = CreateObject("htmlfile")
    htmlDoc.body.innerHTML = http.responseText

    Dim el As Object
    Set el = htmlDoc.getElementById(elementId)
    If Not el Is Nothing Then
        ParseHtmlElement = Trim(el.innerText)
    End If

    Set http = Nothing
    Set htmlDoc = Nothing
End Function

If you need several elements by class or tag, iterate the collection:

Sub ParseAllRows(ByVal url As String)
    Dim http As Object, htmlDoc As Object
    Set http = CreateObject("MSXML2.XMLHTTP")
    http.Open "GET", url, False
    http.setRequestHeader "User-Agent", "Mozilla/5.0"
    http.send

    Set htmlDoc = CreateObject("htmlfile")
    htmlDoc.body.innerHTML = http.responseText

    Dim rows As Object, i As Long
    Set rows = htmlDoc.getElementsByTagName("tr")

    For i = 0 To rows.Length - 1
        ' Write each table row's text to the sheet, starting from row 2
        Cells(i + 2, 1).Value = Trim(rows.Item(i).innerText)
    Next i

    Set http = Nothing
    Set htmlDoc = Nothing
End Sub

Regular expressions

Sometimes the value you want is buried in text with no convenient wrapper. That's where RegExp helps:

Function ExtractByRegex(ByVal text As String, ByVal pattern As String) As String
    Dim re As Object
    Set re = CreateObject("VBScript.RegExp")
    re.Global = False
    re.IgnoreCase = True
    re.pattern = pattern

    Dim matches As Object
    Set matches = re.Execute(text)
    If matches.Count > 0 Then
        ' Return the first capture group
        ExtractByRegex = matches(0).SubMatches(0)
    End If

    Set re = Nothing
End Function

' Example: pull the number out of a string like "Price: 152.34 USD"
' value = ExtractByRegex(s, "Price:\s*([\d.]+)")

Writing the result to the sheet

Writing to cells one at a time is slow. If there are many rows, collect them into an array and dump them in a single assignment:

Sub WriteArrayFast(data() As Variant)
    Dim n As Long
    n = UBound(data) - LBound(data) + 1
    ' Dump the whole column in one operation
    Range("A1").Resize(n, 1).Value = Application.Transpose(data)
End Sub

This kind of bulk write is dozens of times faster than a loop writing to each cell, especially with screen updating turned off:

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
' ... scraping and writing ...
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True

Worked example: scraping a quotes table

Let's assemble a small scraper that walks a list of tickers, requests a price from a hypothetical API, and drops the result on the sheet.

Sub ParseQuotes()
    Dim tickers As Variant
    tickers = Array("AAPL", "MSFT", "GOOGL", "TSLA")

    Dim i As Long, url As String, response As String, price As String

    ' Table headers
    Cells(1, 1).Value = "Ticker"
    Cells(1, 2).Value = "Price"
    Cells(1, 3).Value = "Time"

    Application.ScreenUpdating = False

    For i = LBound(tickers) To UBound(tickers)
        url = "https://example-api.com/quote?symbol=" & tickers(i)
        response = GetResponse(url)              ' function from the section above
        price = ExtractJsonValue(response, "price")

        Cells(i + 2, 1).Value = tickers(i)
        Cells(i + 2, 2).Value = Val(price)
        Cells(i + 2, 3).Value = Now

        ' Pause between requests, so you don't overload the server or get blocked
        Application.Wait Now + TimeValue("0:00:01")
    Next i

    Application.ScreenUpdating = True
    MsgBox "Done: loaded " & (UBound(tickers) + 1) & " quotes", vbInformation
End Sub

This is a simplified skeleton. In practice, scraping stock quotes adds trading-volume parsing, percentage change, historical data, and handling of weekends and off-hours. A full implementation with auto-refresh and conditional formatting is in the web scraping stock data guide.

Error handling and resilience

Network requests fail: the server doesn't answer, a timeout, a bad format. The scraper should survive that rather than crash on the first error.

Function SafeGet(ByVal url As String, Optional retries As Long = 3) As String
    Dim attempt As Long
    For attempt = 1 To retries
        On Error Resume Next
        Dim http As Object
        Set http = CreateObject("WinHttp.WinHttpRequest.5.1")
        http.SetTimeouts 5000, 5000, 10000, 10000   ' resolve, connect, send, receive
        http.Open "GET", url, False
        http.setRequestHeader "User-Agent", "Mozilla/5.0"
        http.send

        If Err.Number = 0 And http.Status = 200 Then
            SafeGet = http.responseText
            On Error GoTo 0
            Exit Function
        End If
        On Error GoTo 0

        ' Back-off before the next attempt grows each time
        Application.Wait Now + TimeValue("0:00:0" & attempt)
    Next attempt

    SafeGet = ""   ' all attempts exhausted
End Function

The WinHttpRequest object is handier than XMLHTTP here precisely because of its SetTimeouts method — you can set explicit wait limits and never hang forever.

Ethics and limits

A few rules that save your nerves and your reputation:

• Read robots.txt and the terms of use. Not every site allows automated collection.
• Pause between requests. Dozens of requests per second look like an attack and get your IP banned. At larger volumes you'd also reach for rotating proxies.
• Prefer official APIs. If the source has an API, use it: it's more stable and legal.
• Don't scrape personal data without a lawful basis and consent (mind GDPR and CCPA).
• Cache the result. If a currency rate updates once a day, don't hit the server every minute.

A no-code alternative: Power Query

It's worth mentioning that for many tasks you don't need VBA at all. Excel's built-in Power Query (Data → Get Data → From Web) can load HTML tables and JSON responses through the UI, with scheduled refresh. If the source serves a clean table or a REST API without tricky auth, Power Query solves the task faster and without a single line of code. VBA is left for cases that need logic, branching, loops over a list, and non-standard parsing.

Summary

VBA scraping is built from a few blocks: an HTTP request (XMLHTTP or WinHttp), parsing the response (string functions, RegExp or HTMLDocument), writing to cells, and error handling. Master that set and you can automate the collection of almost any tabular data right inside a familiar Excel workbook.

Two classic scenarios are great for practice:

• Scraping currency rates — a simple JSON/XML source, ideal for a first scraper.
• Scraping stock quotes — a bit harder: a list of tickers, frequent updates, formatting.

Both are covered in dedicated guides — start with whichever is closer to your task, and the functions described here (GetResponse, ExtractJsonValue, SafeGet) will be the shared foundation for both. And when a spreadsheet macro outgrows the job — you need scale, scheduling, or resilient extraction from defended sites — scraping.pro can deliver the same data as a managed data-as-a-service feed instead.