JSON in VBA lesen und schreiben

In diesem Tutorial laden Sie ein kleines JSON-Dokument, lesen Werte daraus mit JPath, durchlaufen ein Array, ändern das Dokument und speichern es. Sie benötigen dafür etwa zehn Minuten.

• Microsoft 365 Access in 32 oder 64 Bit unter Windows 11 (die unterstützte Konfiguration; siehe Werte und Grenzen).
• WKL Office JSON, registriert für die Bitversion von Office (WKL Office JSON installieren und verteilen).
• Eine Datenbank, der Sie ein Modul hinzufügen können.

Wählen Sie im VBA-Editor (Alt+F11) Extras › Verweise, aktivieren Sie WKL Office JSON 1.2, und klicken Sie auf OK. IntelliSense kennt nun JDoc, JNode und die anderen Klassen.

Fügen Sie ein Standardmodul ein, und legen Sie darin diese Prozedur an:

Public Sub JsonTutorial()
    Dim doc As New JDoc
    Dim text As String
    text = "{""customer"": {""id"": 42, ""name"": ""Müller""}, " & _
           """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 gibt für ungültigen Text False zurück und beschreibt das Problem in ParseError – wie loadXML in MSXML.

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

Jede Get-Methode erwartet einen JPath und gibt einen typisierten Wert zurück. Ohne Ersatzwert löst ein fehlender Pfad den Fehler jeNotFound aus.

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

Pfade ohne $ beginnen an dem Knoten, für den Sie die Methode aufrufen; order.GetLong("id") liest daher die id der jeweiligen Bestellung. GetCurrency gibt den exakten Betrag zurück.

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

SetValue auf dem Dokument legt fehlende Objekte auf dem Weg an. Root("orders") verwendet den Standardmember Item und gibt einen JNode zurück; weisen Sie ihn einer Variablen vom Typ JArray zu, stehen Ihnen die Member des Arrays zur Verfügung. AddElement hängt ein leeres Objekt an und gibt es zurück.

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

ToJson(True) gibt den Text eingerückt aus; Save schreibt UTF-8.

Setzen Sie die Einfügemarke in JsonTutorial, und drücken Sie F5. Öffnen Sie den Direktbereich mit Strg+G.

Der Direktbereich zeigt Müller, 42, ohne, die beiden Bestellungen mit ihren Beträgen (1 19,9 und 2 250 bei deutschen Regionaleinstellungen) sowie das eingerückte JSON mit dem neuen Schlüssel city und einer dritten Bestellung {"id": 3}. Die Datei tutorial.json liegt im Ordner der Datenbank.

• Webdienst aufrufen und seine JSON-Antwort lesen
• JSON mit JPath abfragen: Filter, rekursive Suche, Bereiche (Slices)
• Ungültiges JSON behandeln
• Überblick über das Objektmodell