VBA HTTP Request in Excel — GET and POST with XMLHTTP, and Why the Response Comes Back Stale or Garbled

TL;DR — An HTTP request from VBA gives you back exactly two things: a status code and a block of bytes. It does not tell you whether the data is fresh, what text encoding the bytes use, or whether the request even reached the server in time. Those are your job. Pick the object for the network it has to cross (MSXML2.XMLHTTP uses the user's browser settings and cache, ServerXMLHTTP and WinHttpRequest give you timeouts and no cache), check the status yourself, because a 404 or 500 never raises an error, and decode the bytes on purpose when the server sends anything other than UTF-8.

Dim http As Object
Set http = CreateObject("MSXML2.ServerXMLHTTP.6.0")
http.setTimeouts 5000, 5000, 10000, 30000
http.Open "GET", "https://api.example.com/rates?base=EUR", False
http.send
If http.Status = 200 Then Debug.Print http.responseText

This is the first article in a cluster on getting data from the web into Excel: here the request itself, then VBA JSON for turning the response into a structure, and VBA web scraping for pages that were never meant to be read by 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. Almost every failure happens at one of those borders, not in the request.

What you'll learn

• The mental model: a status and some bytes
• Three objects, three network stacks
• Two kinds of failure: errors that raise and statuses that do not
• Why a GET returns yesterday's data
• Timeouts, and why a request can freeze Excel
• Garbled accents: decoding the bytes yourself
• Sending a POST with a JSON body
• Query strings and API keys

The mental model: a status and some bytes

When you call http.send, VBA hands the request to a Windows network library and waits. What comes back is small: a number (Status, such as 200 or 404), some headers, and the body as bytes. responseText is not the body; it is the body already decoded into text by a guess. responseBody is the raw bytes.

Hold on to that picture and the strange failures stop being strange. Stale data means a layer between you and the server answered instead. Garbled text means the decoding guess was wrong. A macro that "worked yesterday" and now hangs means the network got slower and nothing told the request to give up.

Three objects, three network stacks

The three objects people paste from forums look interchangeable. They are not: each one sits on a different Windows network stack, and the stack decides how the request behaves on your user's machine.

That table explains the most common support ticket: "it works on my laptop and fails at the client's office". The office routes the web through a proxy. XMLHTTP picks that proxy up from the user's settings; ServerXMLHTTP and WinHttpRequest do not, so they cannot connect while the browser on the same machine works fine. Tell them about the proxy explicitly:

http.setProxy 2, "proxy.company.local:8080"     ' 2 = use this proxy

All three are created with CreateObject and need no reference, so the same code runs in 32-bit and 64-bit Office. See VBA CreateObject for late binding in general.

Two kinds of failure: errors that raise and statuses that do not

A request can fail in two places, and VBA reports them through two different channels.

Transport failures raise a run-time error. No network, a server name that does not resolve, a timeout, a TLS handshake that fails: send stops with a run-time error, usually a large negative number such as -2147012889 (the server name could not be resolved) or -2147012894 (the operation timed out). Trap these with On Error.

HTTP failures do not raise anything. A 404, a 401 because the key expired, a 500 because the server broke: send returns normally, Status holds the code, and responseText holds an error page or an error JSON. Code that skips the status check happily writes "Unauthorized" or a page of HTML into your sheet.

So every request needs both checks. Put them in one function and never call send anywhere else:

Function HttpGet(ByVal url As String) As String
    Dim http As Object
    Set http = CreateObject("MSXML2.ServerXMLHTTP.6.0")
    http.setTimeouts 5000, 5000, 10000, 30000
    http.Open "GET", url, False
    http.setRequestHeader "Accept", "application/json"
    http.send                                   ' transport errors raise here
    If http.Status < 200 Or http.Status >= 300 Then
        Err.Raise vbObjectError + 513, "HttpGet", _
            "HTTP " & http.Status & " " & http.statusText & " for " & url
    End If
    HttpGet = http.responseText
End Function

Now both kinds of failure arrive as a run-time error with a readable message, and the caller handles them in one place. See VBA error handling for the pattern around it.

Why a GET returns yesterday's data

MSXML2.XMLHTTP goes through WinINet, and WinINet keeps a cache. If the server's response allows caching, or says nothing about it, a repeated GET to the same URL can be answered from the cache without reaching the server at all. The macro runs, the status is 200, and the exchange rates are from this morning.

Three ways out, from best to worst:

1. Use ServerXMLHTTP or WinHttpRequest, which have no cache.
2. If you must stay on XMLHTTP (for its proxy handling), force the cache to ask the server again by sending a date long in the past: http.setRequestHeader "If-Modified-Since", "Sat, 01 Jan 2000 00:00:00 GMT".
3. Make each URL unique with a throwaway parameter, such as "&_=" & Format(Now, "yyyymmddhhnnss"). This works, but some APIs reject parameters they do not know.

The tell-tale sign of the cache is a request that returns instantly with exactly the same data as last time, while the same URL in a browser shows something newer.

Timeouts, and why a request can freeze Excel

The False in http.Open "GET", url, False makes the request synchronous: VBA waits on that line until the response arrives. While it waits, Excel cannot repaint or respond, and Windows labels the window Not Responding. A slow API turns into a frozen Excel; a dead one, without a timeout, can freeze it for a long time.

setTimeouts takes four values in milliseconds: name resolution, connect, send, and receive. Set them to numbers that match the API. 5000, 5000, 10000, 30000 means: give up if the server cannot be found or reached in five seconds, or if it has not answered in thirty. XMLHTTP has no such method, which is one more reason to prefer ServerXMLHTTP for anything a user waits on.

For a loop over many requests, update the status bar between calls so the user can see progress. Asynchronous requests exist (True as the third argument), but polling them from VBA needs a DoEvents loop and adds more failure modes than it removes; keep requests synchronous and short.

Garbled accents: decoding the bytes yourself

responseText decodes the bytes using the character set the server declares in its Content-Type header, and assumes UTF-8 when the server declares none. Most modern APIs send UTF-8 and say so, and everything works. An older server that sends Windows-1252, ISO-8859-1 or Shift_JIS without saying so arrives as München or as question marks and boxes.

When that happens, skip responseText, take the raw bytes and decode them with the right character set:

Function BytesToText(ByVal bytes As Variant, ByVal charset As String) As String
    With CreateObject("ADODB.Stream")
        .Type = 1                  ' binary
        .Open
        .Write bytes
        .Position = 0
        .Type = 2                  ' text
        .Charset = charset         ' "windows-1252", "iso-8859-1", "shift_jis"
        BytesToText = .ReadText
        .Close
    End With
End Function

body = BytesToText(http.responseBody, "windows-1252")

One trap while you debug this: do not judge the text in the Immediate window. The VBA editor can only show characters from the Windows system code page, so correct Japanese text prints as ??? on a German Windows, and correct German text can print wrongly on a Japanese one. Write the string to a cell; the cell shows the truth.

Sending a POST with a JSON body

A POST is the same call with a method, a content type and a body:

http.Open "POST", "https://api.example.com/orders", False
http.setRequestHeader "Content-Type", "application/json"
http.send "{""sku"":""A-100"",""qty"":3}"

The trap is building that body by gluing numbers into a string. VBA converts a number to text with the Windows regional settings, so on a German, French or Spanish machine "{""price"":" & 3.5 & "}" produces {"price":3,5}, which is not valid JSON. The same macro works in New York and fails in Munich. Either convert numbers with Trim$(Str$(x)), which always uses a point (see VBA Str), or better, build the body with a JSON library as shown in VBA JSON.

Query strings and API keys

Values in a URL must be percent-encoded: a space, an ampersand or an umlaut in a query parameter breaks the request or silently changes it. Excel 2013 and later have a function for it, and it encodes accented letters as UTF-8, so Zürich becomes Z%C3%BCrich:

url = "https://api.example.com/search?city=" & _
      Application.WorksheetFunction.EncodeURL(Range("B2").Value)
' B2 = Zurich & Geneva  ->  city=Zurich%20%26%20Geneva

API keys go in a header, usually http.setRequestHeader "Authorization", "Bearer " & apiKey. They do not go in the code. Anyone who has the workbook can open the VBA editor, and a VBA project password is not real protection. Read the key at run time from a file in the user's profile or from an environment variable with Environ, so the workbook can be shared without sharing the key.

The judgment call: one request function, three checks

Most broken HTTP macros are not wrong about HTTP. They skip one of the three checks the library will not do for you: did it arrive in time, did the server say yes, and is the text decoded right. So write one request function that sets timeouts, checks the status and decodes on purpose, and make it the only place in the project that calls send.

For the object, default to MSXML2.ServerXMLHTTP.6.0: no stale cache, real timeouts. Switch to MSXML2.XMLHTTP.6.0 only when users sit behind a proxy you cannot configure, and then add the If-Modified-Since header. And if all you need is to pull the same table on every refresh, with no logic around it, consider Power Query's From Web before writing any VBA at all.

How ExcelMaster helps

API macros fail on other people's machines: a proxy you have never seen, a Windows set to a different language, a server that answers slowly on Monday mornings.

ExcelMaster writes the request code for the workbook in front of it, checks the status and the decoded result before anything reaches your cells, and shows you what the API actually returned when it does not match what you expected.

Frequently asked questions

How do I make an HTTP GET request in Excel VBA?

Create MSXML2.ServerXMLHTTP.6.0 with CreateObject, call .Open "GET", url, False, then .send, check .Status, and read .responseText. No reference is needed.

What is the difference between XMLHTTP and ServerXMLHTTP?

XMLHTTP uses WinINet: it follows the user's proxy settings and may cache GET responses, and it has no timeout setting. ServerXMLHTTP uses WinHTTP: no cache and real timeouts, but you may need to set a proxy with setProxy.

Why does my VBA request return old data?

MSXML2.XMLHTTP can answer a repeated GET from the WinINet cache. Switch to ServerXMLHTTP, or send an old If-Modified-Since date with setRequestHeader before send.

Why doesn't VBA raise an error on a 404 or 500?

Because the request itself succeeded; the server answered. Only transport failures such as no network or a timeout raise a run-time error. Check .Status after every send.

Why is the response text garbled?

The server sent text in an encoding it did not declare, and responseText assumed UTF-8. Decode .responseBody with ADODB.Stream and the correct Charset.

Tested in

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

Related guides: VBA JSON · VBA Web Scraping · VBA CreateObject · VBA Error Handling · VBA On Error · VBA Str · VBA Environ · VBA StatusBar