How to: Call a web service and read its JSON response

Request data from a REST service in VBA and read fields of the JSON response.

MSXML2.ServerXMLHTTP.6.0 (part of Windows) sends the request; JDoc.LoadJson parses responseText; the getters read the fields. LoadJson returning False and the HTTP status are checked separately, because a service can answer an error with a body that is not JSON.

Public Function GetCustomerName(ByVal customerId As Long) As String
    Dim http As Object
    Dim doc As New JDoc
 
    Set http = CreateObject("MSXML2.ServerXMLHTTP.6.0")
    http.Open "GET", "https://api.example.com/customers/" & customerId, False
    http.setRequestHeader "Accept", "application/json"
    http.send
 
    If http.Status <> 200 Then
        Err.Raise vbObjectError + 1000, "GetCustomerName", "HTTP " & http.Status & ": " & http.statusText
    End If
 
    If Not doc.LoadJson(http.responseText) Then
        Err.Raise vbObjectError + 1001, "GetCustomerName", _
            "Invalid JSON in line " & doc.ParseError.Line & ": " & doc.ParseError.Reason
    End If
 
    GetCustomerName = doc.GetString("$.name", "")
End Function

To send JSON, build a document and pass its text:

Dim body As New JDoc
body.SetValue "$.name", "Miller"
body.SetValue "$.active", True
http.Open "POST", "https://api.example.com/customers", False
http.setRequestHeader "Content-Type", "application/json; charset=utf-8"
http.send body.Json

• responseText decodes the body with the charset the server declares; services that send UTF-8 without declaring it may show wrong umlauts. Then decode responseBody with an ADODB.Stream (Charset = "utf-8") before LoadJson.
• The synchronous call (False in Open) blocks Access until the service answers; set http.setTimeouts for slow services.
• The URL above is an example; authentication headers depend on the service.

• Handle invalid JSON
• `JDoc.LoadJson`, `JDoc.Json`
• Overview