Read and write JSON in VBA

This tutorial loads a small JSON document, reads values from it with JPath, loops over an array, changes the document and saves it. It takes about ten minutes.

• Microsoft 365 Access, 32-bit or 64-bit, on Windows 11 (the supported configuration; see Values and limits).
• WKL Office JSON registered for the Office bitness (Install and deploy).
• A database you can add a module to.

In the VBA editor (Alt+F11), choose Tools › References, enable WKL Office JSON 1.2 and click OK. IntelliSense now knows JDoc, JNode and the other classes.

Insert a standard module and add this procedure:

Public Sub JsonTutorial()
    Dim doc As New JDoc
    Dim text As String
    text = "{""customer"": {""id"": 42, ""name"": ""Miller""}, " & _
           """orders"": [{""id"": 1, ""total"": 19.90}, {""id"": 2, ""total"": 250.00}]}"
 
    If Not doc.LoadJson(text) Then
        Debug.Print "Invalid JSON: "; doc.ParseError.Reason
        Exit Sub
    End If

LoadJson returns False for invalid text and describes the problem in ParseError, like loadXML in MSXML.

    Debug.Print doc.GetString("$.customer.name")         ' Miller
    Debug.Print doc.GetLong("$.customer.id")             ' 42
    Debug.Print doc.GetString("$.customer.fax", "none")  ' none: the default for a missing path

Each getter takes a JPath and returns a typed value. Without a default, a missing path raises the error jeNotFound.

    Dim order As JNode
    For Each order In doc.SelectNodes("$.orders[*]")
        Debug.Print order.GetLong("id"), order.GetCurrency("total")
    Next

Paths without $ start at the node they are called on, so order.GetLong("id") reads the id of each order. GetCurrency returns the exact amount.

    doc.SetValue "$.customer.city", "Chicago"
 
    Dim orders As JArray
    Set orders = doc.Root("orders")
    orders.AddElement.SetValue "id", 3

SetValue on the document creates missing objects on the way. Root("orders") uses the default member Item and returns a JNode; assigning it to a JArray variable gives access to the array's own members. AddElement appends an empty object and returns it.

    Debug.Print doc.ToJson(True)
    doc.Save CurrentProject.Path & "\tutorial.json"
End Sub

ToJson(True) indents the text; Save writes UTF-8.

Place the cursor in JsonTutorial and press F5. Open the Immediate window with Ctrl+G.

The Immediate window shows Miller, 42, none, the two orders with their totals (1 19.9 and 2 250 with US regional settings), and the indented JSON with the new city key and a third order {"id": 3}. The file tutorial.json is in the folder of the database.

• Call a web service and read its JSON response
• Query JSON with JPath: filters, recursive search, slices
• Handle invalid JSON
• Overview of the object model