In Zeiten zunehmender Vernetzung spielt natürlich auch das Json-Format eine gro�e Rolle, zunehmend auch für Excel. Das Senden und Empfangen der Daten ist kein Problem, während die Analyse dieser Daten mit VBA vergleichsweise umständlich ist. Einfache Möglichkeiten, wie beispielsweise mit jQuery, geben die Bordmittel von VBA nicht her.
Wesentlich vereinfacht wird dies jedoch mit dem JsonConverter von Tim Hall, den es hier zum Download gibt: VBA-tools/VBA-JSON. Zur Installation müssen (alles für Windows) nur zwei Schritte erfolgen:
- Modul JsonConverter.bas in das VBA-Projekt einfügen
- im Editor unter Extras ▸ Verweise ein Häkchen bei Microsoft Scripting Runtime setzen
Damit steht schon die Funktion JsonConverter.ParseJson() zur Verfügung.
Eine Datenebene
Das Verwenden soll hier zunächst an einem einfachen Beispiel gezeigt werden. Dazu nutzen wir dieses PHP-Script, das einen Adressdatensatz als Json-Objekt zur Verfügung stellt, den wir mit VBA abrufen:
$a['vorname'] = "Max";
$a['nachname'] = "Mütze";
$a['strasse'] = "Mützenweg 55";
$a['plz'] = "01234";
$a['ort'] = "Mützenhausen";
echo json_encode($a);
Zunächst rufen wir das Ganze vom Server ab:
strURL = "https://example.org/jsonabfrage.php"
strPostDaten = "" 'Hier käme etwas rein, wenn was an den Server übermittelt werden sollte
With CreateObject("MSXML2.XMLHTTP")
.Open "POST", strURL, False
.setRequestHeader "Content-Type", "application/x-www-form-urlencoded"
.Send (strPostDaten)
Damit empfangen wir .ResponseText. Zum Vermeiden von Fehlern durch den JsonConverter ist hier schon wichtig, diese Rückgabe zu prüfen. Im einfachsten Fall mit If InStr(1, .ResponseText, "{") > 0 Then.
In .ResponseText steckt nun dieses Objekt:
Das weisen wir mit der Funktion JsonConverter.ParseJson(.ResponseText) einer Variablen zu, die als Dictionary-Objekt deklariert ist:
Set dicParsed = JsonConverter.ParseJson(.ResponseText)
In der Variablen sind nun die einzelnen Einträge aus dem ResponseText als Key=Value-Paare enthalten. Da die Variable dicParsed ein Objekt ist, verfügt sie über verschiedene Eigenschaften, mit denen wir arbeiten können. So können wir z. B. einfach eine Schleife über die Keys laufen lassen und haben somit deren Werte:
strAusgabe = strAusgabe & dicParsed.Count & " Einträge:" & vbNewLine & vbNewLine
For Each varWert In dicParsed.Keys
strAusgabe = strAusgabe & varWert & ": " & dicParsed(varWert) & vbNewLine
Next varWert
MsgBox strAusgabe
Das komplette VBA-Script zum Testen:
Sub Post_JSONAusgabe_1()
Dim strURL As String, strPostDaten As String
Dim dicParsed As Dictionary
Dim varWert As Variant, strAusgabe As String
strURL = "https://example.org/jsonabfrage.php"
strPostDaten = "" 'Hier käme etwas rein, wenn was an den Server übermittelt werden sollte
With CreateObject("MSXML2.XMLHTTP")
.Open "POST", strURL, False
.setRequestHeader "Content-Type", "application/x-www-form-urlencoded"
.Send (strPostDaten)
'Die Antwort muss geprüft werden, da sonst ein Fehler beim JsonConverter auftritt.
'Hier einfach auf das Vorhandensein der geschweiften Klammer:
If InStr(1, .ResponseText, "{") > 0 Then
strAusgabe = ""
Set dicParsed = JsonConverter.ParseJson(.ResponseText)
strAusgabe = strAusgabe & dicParsed.Count & " Einträge:" & vbNewLine & vbNewLine
For Each varWert In dicParsed.Keys
strAusgabe = strAusgabe & varWert & ": " & dicParsed(varWert) & vbNewLine
Next varWert
MsgBox strAusgabe
Else
MsgBox "Schiefgegangen: " & .ResponseText, vbOKOnly + vbCritical, "Fehler"
End If
End With
End Sub
Mehrere Datenebenen
Häufig haben wir es jedoch mit Verschachtelungen zu tun, dass also in einem Array-Element ein weiterer Array (bzw. in einem Json-Element ein weiteres Json-Objekt) steckt. im vorigen Beispiel waren alle Elemente auf einer Ebene, aber genau so könnten die Elemente insgesamt ein Wert eines übergeordneten Elements sein:
- values
- vorname="Max"
- nachname="Mütze"
- strasse="Mützenweg 55"
- plz="01234"
- ort="Mützenhausen"
Wir hätten hier also den Key â??valuesâ??, dem als Wert ein weiterer Array, bestehend aus Key-Value-Paaren, zugeordnet ist. Ein Beispiel, wie dies mit PHP möglich wäre:
$a['vorname'] = "Max";
$a['nachname'] = "Mütze";
$a['strasse'] = "Mützenweg 55";
$a['plz'] = "01234";
$a['ort'] = "Mützenhausen";
$b['values'] = $a;
echo json_encode($b);
In VBA würde somit dieses Json-Objekt ankommen:
Zum Dekodieren verwenden wir wieder die bekannte Funktion und weisen das Ganze der Dictionary-Variablen zu:
Set dicParsed = JsonConverter.ParseJson(.ResponseText)
Wenn wir nun an die relevanten Inhalte wollen, müssen wir beachten, dass die als Array (bzw. genauer als Dictionary) im übergeordneten Schlüssel â??valuesâ?? stecken. Das heiÃ?t, dass wir den ansprechen müssen. Wir durchlaufen also die einzelnen Elemente in dicParsed("values"):
'Hier werden nur die Einträge aus â??valuesâ?? verwendet, also das, was in PHP in $a steckt:
For Each varWert In dicParsed("values").Keys
strAusgabe = strAusgabe & varWert & ": " & dicParsed("values")(varWert) & vbNewLine
Next varWert
Hier ist das gesamte Script zum Testen, zum Schluss ergänzt um ein paar Ausgaben zur Verdeutlichung:
Sub Post_JSONAusgabe_2()
'PHP:
'Beachten: Hier gibt es eine Verschachtelung.
'Im Array $a sind die eigentlichen Daten enthalten.
'$a['vorname'] = "Max";
'$a['nachname'] = "Mütze";
'$a['strasse'] = "Mützenweg 55";
'$a['plz'] = "01234";
'$a['ort'] = "Mützenhausen";
'Zuletzt wird $a jedoch dem Array $b zugewiesen, hier dem Key â??valuesâ??:
'$b['values'] = $a;
'Im Json ist also diese Verschachtelung auch enthalten und wird so ausgegeben:
'echo json_encode($b);
Dim strURL As String, strPostDaten As String
Dim dicParsed As Dictionary
Dim varWert As Variant, strAusgabe As String
strURL = "https://example.org/jsonabfrage.php"
strPostDaten = ""
With CreateObject("MSXML2.XMLHTTP")
.Open "POST", strURL, False
.setRequestHeader "Content-Type", "application/x-www-form-urlencoded"
.Send (strPostDaten)
If .ResponseText <> "" Then 'Anpassen, auf Fehler prüfen!
strAusgabe = ""
'dicParsed ist der komplette(!) verschachtelte Array:
Set dicParsed = JsonConverter.ParseJson(.ResponseText)
'Hier werden nur die Einträge aus â??valuesâ?? verwendet, also das, was in PHP in $a steckt:
For Each varWert In dicParsed("values").Keys
strAusgabe = strAusgabe & varWert & ": " & dicParsed("values")(varWert) & vbNewLine
Next varWert
MsgBox "In PHP in $a enthalten:" & vbNewLine & vbNewLine & strAusgabe
'Nun wird der Array um einen Eintrag erweitert (entspräche in PHP $a['alter'] = 32;):
'dicParsed("values")("alter") = 32
dicParsed("values").Add "alter", 32
strAusgabe = "Das gesamte Json-Objekt ist nun:" & vbNewLine & vbNewLine
strAusgabe = strAusgabe & JsonConverter.ConvertToJson(dicParsed) 'komplettes Json-Objekt
strAusgabe = strAusgabe & vbNewLine & vbNewLine
strAusgabe = strAusgabe & "während im Key â??valuesâ?? (ehemals $b) nur das enthalten ist:" & vbNewLine & vbNewLine
strAusgabe = strAusgabe & JsonConverter.ConvertToJson(dicParsed("values")) 'Inhalt nur von "values"
MsgBox strAusgabe
Else
MsgBox "Schiefgegangen.", vbOKOnly + vbCritical, "Fehler"
End If
End With
End Sub
Download: excel_php_json.xlsm
Suchergebnisse (7 Treffer, 1 Begriff) 
Kategorie: 
