<\/p>\n
Webservices bedienen sich verschiedener Formate, um ihre Ergebnisse zu liefern. Meist wird zumindest XML angeboten, manchmal gibt es die Daten zusätzlich im JSON-Format. Beides kein Problem, da wir den Umgang mit XML-Dokumenten schon in verschiedenen Beiträgen beschrieben haben. Wenn allerdings nur JSON zurückgeliefert wird, müssen wir uns etwas einfallen lassen – es gibt nämlich kein Pendant zur MSXML-Bibliothek, mit der sich die Inhalte leicht auslesen lassen. Aber es gibt ja VBA – und damit bauen wir uns unseren eigenen Parser.<\/p>\n
Bei JSON <\/b>handelt es sich um die JavaScript Object Notation<\/b>. Der Aufbau ähnelt ein wenig dem Aufbau von XML-Dateien, unterscheidet sich aber in manchen Punkten. Schauen wir uns zunächst an, wie solch ein Dokument aufgebaut ist und welche Elemente es enthalten kann.<\/p>\n
Ein einfaches JSON<\/b>-Dokument sieht so aus:<\/p>\n Es besteht in diesem Fall aus zwei Objekten, wobei der Ausdruck vor dem Doppelpunkt dem Objektnamen entspricht und der hinter dem Doppelpunkt dem Wert.<\/p>\n Etwas komplexer wird es, wenn ein Element ein Unterelement besitzt. In diesem Fall werden die Informationen zum Autor eines Artikels in einem Unterelement gespeichert. <\/p>\n Das Unterelement ist wie das Hauptelement aufgebaut und dementsprechend in geschweifte Klammern eingefasst:<\/p>\n Schließlich gibt es noch Aufzählungen, also Elemente, die einen Objektnamen und mehrere Elemente besitzen. Das folgende Beispiel listet die Hobbys einer Person auf, wobei diese in eckige Klammern eingefasst werden:<\/p>\n Nun sind leider nicht alle Elemente in Anführungszeichen eingefasst, sondern nur Zeichenketten.<\/p>\n Es gibt jedoch noch weitere Datentypen, die wie folgt formatiert werden – hier eine übersicht über alle Varianten:<\/p>\n Außerdem kann der Wert eines JSON<\/b>-Elements, wie bereits oben in den Beispielen angeführt, ein weiteres Element oder ein Array von Elementen sein – unter http:\/\/www.json.org\/ <\/b>wird dies schön dargestellt.<\/p>\n Damit ist ein JSON<\/b>-Element zwar nicht durch eine einfache Routine zu parsen, aber der Aufwand hält sich dennoch in Grenzen.<\/p>\n In der Beispieldatenbank finden Sie die nachfolgend vorgestellten Funktionen zum Parsen eines JSON<\/b>-Dokuments im Modul mdlJSONParser<\/b>.<\/p>\n Ergebnis nach dem Parsen<\/b><\/p>\n Bevor man sich an die Programmierung eines Parsers begibt, sollte man sich zunächst Gedanken darüber machen, welches Ergebnis man erhalten möchte und wie man dieses weiter verarbeiten kann.<\/p>\n Ein JSON<\/b>-Element enthält im einfachsten Fall einfache Informationen, die man beispielsweise in einen Datensatz einer Tabelle schreibt, in anderen Fällen aber auch verschachtelte Elemente, für deren Speicherung man eine Reihe verknüpfter Tabellen benötigen würde. Dies ist ja bereits bei dem Beispiel mit der Person und ihren Hobbys der Fall (siehe oben).<\/p>\n Sie werden die Daten aber nicht immer in eine oder mehrere Tabellen schreiben wollen, sondern möchten diese vielleicht auch einmal ad hoc auswerten.<\/p>\n Vielleicht möchten Sie dabei noch nicht einmal auf alle Daten zugreifen, sondern nur bestimmte Elemente auslesen.<\/p>\n Dies ist beispielsweise der Fall, wenn Sie ein JSON<\/b>-Dokument von einem Webservice erhalten und etwa den Fehlercode auslesen möchten. Rufen Sie beispielsweise einmal im Internet-Browser die folgende URL auf:<\/p>\n Dann erhalten Sie von Facebook das folgende JSON<\/b>-Dokument als Antwort:<\/p>\n In diesem Fall möchten Sie vielleicht nur auf den Wert des Attributs code <\/b>zugreifen. Wie kann man die Informationen nach dem Parsen also am besten zugänglich machen<\/p>\n Wenn Sie immer mit den gleichen Strukturen arbeiten, könnten Sie dies beispielsweise mit einer oder mehreren Klassen erledigen, die Sie mit den entsprechenden Eigenschaften versehen. Bei vielen verschiedenen Aufrufen der Funktionen eines Webservices würde dies jedoch den Rahmen sprengen – wir benötigen eine flexiblere Variante.<\/p>\n Speichern des geparsten JSON-Dokuments in Collections<\/b><\/p>\n Die Lösung ist das Collection<\/b>-Objekt: Es bietet genau die Möglichkeiten, die für die Unterbringung der in einem JSON<\/b>-Dokument enthaltenen Informationen nötig sind. In diesem Fall bedeutet dies:<\/p>\n Für das erste Beispiel von oben sieht dies beispielsweise wie in Listing 1 aus. Zunächst deklarieren und instanzieren Sie das Objekt:<\/p>\n Listing 1: Hinzufügen und Abrufen von Collection-Elementen<\/span><\/b><\/p>\n Dann fügen Sie die Name-Wert-Paare hinzu, wobei der Wert zuerst für den Parameter Item <\/b>und dann der Name für den Parameter Key <\/b>angegeben wird:<\/p>\n Schließlich können Sie die Werte einfach über den Namen abfragen – sowohl über die Syntax mit Ausrufezeichen als auch als Auflistungselement:<\/p>\n Im zweiten Beispiel mit dem Autor als Unterobjekt sieht der Einsatz des Collection<\/b>-Objekts wie in Listing 3 aus.<\/p>\n Hier deklarieren wir zwei Objekte – eines für den Artikel und eines für den Autor des Artikels. Beide werden zunächst mit den als Zeichenkette vorliegenden Daten gefüllt, bevor die eine Collection<\/b> der anderen als weiterer Eintrag hinzugefügt wird. Interessant wird der Zugriff auf die in den Collection<\/b>-Objekten enthaltenen Daten. Auf Titel <\/b>und Autor <\/b>greifen Sie wie gewohnt zu. Auf die Unterelemente können Sie ebenfalls per Ausrufezeichen-Referenzierung und über die Auflistung zugreifen – die verschiedenen Collection<\/b>-Objekte werden dabei einfach aneinandergereiht. Im Falle eines Datenfeldes hilft das Collection<\/b>-Objekt ebenfalls weiter. Hier legen Sie wiederum eine Collection für die Personen-Daten an und eines für die Hobbys.<\/p>\n Die Hobbys werden der Collection colHobbys <\/b>nacheinander hinzugefügt, wobei wir einen durchnummerierten Schlüssel verwenden (Hobby1<\/b>, Hobby2 <\/b>und Hobby3<\/b>).<\/p>\n Diese können Sie später bei der Referenzierung der enthaltenen Texte verwenden. Sie können aber auch direkt über die Auflistung des Collection<\/b>-Objekts Hobbys darauf zugreifen – s.-Listing 2.<\/p>\n Listing 2: Zugriff auf eine Collection mit mehreren gleichartigen Elementen<\/span><\/b><\/p>\n Da es unter JSON <\/b>keine anderen Strukturen gibt, reicht das Dictionary<\/b>-Objekt also völlig aus, um die in einem JSON<\/b>-Dokument enthaltenen Daten zu speichern und zu referenzieren.<\/p>\n Fehlt nur noch der Code, um das JSON<\/b>-Dokument zu parsen und in ein oder mehrere Collection<\/b>-Objekte zu schreiben.<\/p>\n JSON parsen<\/b><\/p>\n Die nachfolgend vorgestellten Prozeduren und Funktionen lehnen sich weitgehend an ein im Internet gefundenes Beispiel an (siehe https:\/\/code.google.com\/p\/vba-json\/<\/b>). Wir haben dort jedoch ein paar änderungen vorgenommen – zum Beispiel, um die Referenzierung der Objekte im Direktbereich anzuzeigen.<\/p>\n Den Beginn macht die Funktion JSONParsen<\/b>, welche das JSON<\/b>-Dokument als Parameter erwartet (s. Listing 3). Diese Routine ruft zunächst eine weitere Funktion auf, welche überflüssige Steuerzeichen entfernt – wie beispielsweise Zeilenumbrüche oder Leerzeichen außerhalb der Zeichenketten.<\/p>\n Listing 3: Parsen eines JSON-Dokuments<\/span><\/b><\/p>\n Steuerzeichen entfernen<\/b><\/p>\n Diese Funktion heißt SteuerzeichenUndLeerzeichenEntfernen<\/b> und sieht wie in Listing 4 aus. Die Funktion erwartet die zu ändernde Zeichenkette als Parameter und durchläuft diese Zeichen für Zeichen. Das aktuelle Zeichen speichert die Prozedur jeweils in der Variablen strAktuell<\/b>.<\/p>\n <\/p>\n Listing 4: Entfernen der Steuerzeichen aus einer Zeichenkette<\/span><\/b><\/p>\n Die Prozedur soll Steuer- und Leerzeichen nur außerhalb der eigentlichen Werte des JSON<\/b>-Dokuments entfernen. Da diese Werte entweder in Anführungszeichen eingefasst sind oder aber keine Leerzeichen enthalten (Zahlen et cetera), muss für jedes Zeichen zunächst geprüft werden, ob es sich innerhalb oder außerhalb einer mit Anführungszeichen eingefassten Zeichenkette befindet.<\/p>\n Deshalb prüft die Prozedur für jedes Zeichen, ob es sich dabei im ein Anführungszeichen handelt. Falls ja, wird die Variable bolAnfuehrungszeichen <\/b>beim ersten Aufeinandertreffen mit einem Anführungszeichen auf den Wert True <\/b>eingestellt.<\/p>\n Bei jedem folgenden Anführungszeichen stellt die Prozedur bolAnfuehrungszeichen <\/b>auf True<\/b>, wenn diese Variable vorher den Wert False <\/b>hatte und umgekehrt. So kann die Prozedur jederzeit ermitteln, ob sich das aktuelle Zeichen innerhalb von Anführungszeichen befindet.<\/p>\n Die folgende Zeile prüft anhand der Funktion IstSteuerzeichen<\/b>, ob es sich bei dem Zeichen um eines der Steuerzeichen vbCrLf<\/b>, vbCr<\/b>, vbLf <\/b>oder vbTab <\/b>handelt (s. Listing 5). Ist dies der Fall, geschieht nichts. Anderenfalls können zwei interessante Fälle vorliegen:<\/p>\n Listing 5: Steuerzeichen finden<\/span><\/b><\/p>\n Auf diese Weise stellt SteuerzeichenUndLeerzeichenEntfernen <\/b>eine neue Zeichenkette aus allen Zeichen des JSON<\/b>-Dokuments zusammen, die kein Steuerzeichen und kein Leerzeichen außerhalb geschlossener Anführungszeichen sind. Warum das Ganze Weil JSON<\/b>-Dokumente der besseren Lesbarkeit halber gern mit Zeilenumbrüchen und Einrückungen gesendet werden (s. Bild 1), die beim späteren Parsen aber eher stören würden. Daher extrahieren wir diese in einem ersten Schritt.<\/p>\n Bild 1: Ein JSON-Dokument im Webbrowser<\/span><\/b><\/p>\n Danach geht es mit der zuerst aufgerufenen Funktion JSONParsen <\/b>weiter. Die Prozedur hat eine Laufvariable namens l <\/b>des Datentyps Long <\/b>deklariert, welche die aktuelle Position beim Durchlaufen des JSON<\/b>-Dokuments markiert. Beim eigentlichen Parsen betrachten wir also wiederum Zeichen für Zeichen.<\/p>\n Das erste Zeichen eines JSON<\/b>-Dokuments muss eine öffnende geschweifte oder eckige Klammer sein ({<\/b> oder [<\/b>). Im Falle einer geschweiften Klammer beginnt das Dokument mit einem einfachen Element, bei einer eckigen Klammer enthält das Dokument ein Array von Elementen.<\/p>\n Bei der geschweiften Klammer fährt die Routine mit dem Aufruf der Funktion NameWertParsen <\/b>fort, bei einer eckigen Klammer mit ListeParsen<\/b>. Das Ergebnis kommt in jedem Falle in Form eines Collection<\/b>-Objekts, das auch gleich als Funktionswert an die aufrufende Instanz weitergegeben wird.<\/p>\n Name-Wert-Elemente parsen<\/b><\/p>\n Der einfache Fall eines Name-Wert-Elements sieht etwa so aus:<\/p>\n Die Prozedur NameWertParsen <\/b>(s. Listing 6) geht damit wie folgt vor: Sie durchläuft die mit strJSON <\/b>übergebene Zeichenkette, die beispielsweise wie im Beispiel oben aussieht, in einer Do While<\/b>-Schleife, bis sie auf eine schließende geschweifte Klammer stößt.<\/p>\n Listing 6: Parsen von Name-Wert-Paaren<\/span><\/b><\/p>\n Innerhalb der Do While<\/b>-Schleife wird die Zähler-Variable l <\/b>jeweils um eins erhöht. Die Funktion weiß, dass hinter der geschweiften Klammer immer zunächst der Schlüssel beziehungsweise Name des Elements folgt (im Beispiel Beispielname<\/b>). Dieser wird in der Funktion SchluesselParsen <\/b>ermittelt (s. Listing 7).<\/p>\n Listing 7: Ermitteln des Schlüssels eines Elements<\/span><\/b><\/p>\n Diese erwartet das JSON<\/b>-Dokument sowie die aktuelle Position als Parameter. Sie durchläuft die in strJSON<\/b> gespeicherte Zeichenkette höchstens vom ersten bis zum letzten Zeichen. Dabei treten drei verschiedene Fälle auf:<\/p>\n Schließlich gibt die Funktion den in strSchluessel <\/b>zusammengesetzten Wert an die aufrufende Routine zurück. Damit sind wir wieder mittendrin in der Funktion NameWertParsen<\/b>. Diese hat nun den Schlüssel, benötigt aber noch den Wert, um einen ersten Eintrag im Collection<\/b>-Objekt anzulegen.<\/p>\n Dies geschieht in der nächsten Anweisung, wo gleichzeitig der Wert ermittelt und der neue Eintrag im Collection<\/b>-Objekt erzeugt wird. Der zweite Parameter der dort verwendeten Add<\/b>-Methode der Collection col <\/b>ist der in strSchluessel <\/b>gespeicherte Name des Elements. Der Wert wird direkt als erster Parameter übergeben, und zwar mit der Funktion WertParsen<\/b>:<\/p>\n Das Ergebnis der Funktion WertParsen <\/b>weisen wir direkt als Wert des Collection<\/b>-Elements zu, weil es sowohl eine Zeichenkette als auch ein Zahlenwert oder ein weiteres Collection<\/b>-Objekt sein kann.<\/p>\n Die Funktion WertParsen <\/b>sieht wie in Listing 8 aus und ist im Grunde nur eine Weiterleitung zu einer von mehreren weiteren Funktionen, die typ-abhängig das neue Element zurückliefern.<\/p>\n{\r\n \"Vorname\":\"André\",\r\n \"Nachname\":\"Minhorst\"\r\n}<\/pre>\n{\r\n \"Titel\":\"JSON parsen mit VBA\",\r\n \"Ausgabe\":\"4\/2013\",\r\n \"Autor\":{\r\n \"Vorname\":\"André\",\r\n \"Nachname\":\"Minhorst\"\r\n }\r\n}<\/pre>\n{\r\n \"Vorname\":\"André\",\r\n \"Nachname\":\"Minhorst\",\r\n \"Hobbys\":[\r\n \"Lesen\",\"Reiten\",\"Programmieren\"\r\n ]\r\n}<\/pre>\n\n
https:\/\/graph.facebook.com\/me\/feed<\/pre>\n
{\r\n \"error\": {\r\n \"message\": \"An active access token must be used to query information about the current user.\",\r\n \"type\": \"OAuthException\",\r\n \"code\": 2500\r\n }\r\n}<\/pre>\n\n
Dim <\/span>colPerson As <\/span>Collection\r\nDim <\/span>colBeitrag As <\/span>Collection\r\nSet<\/span> colPerson = New<\/span> Collection\r\ncolPerson.Add \"André\", \"Vorname\"\r\ncolPerson.Add \"Minhorst\", \"Nachname\"\r\nSet<\/span> colBeitrag = New<\/span> Collection\r\nWith<\/span> colBeitrag\r\n .Add \"JSON parsen mit VBA\", \"Titel\"\r\n .Add \"4\/2013\", \"Ausgabe\"\r\n .Add colPerson, \"Autor\"\r\nEnd With<\/span>\r\nDebug.Print<\/span> \"Titel:\", colBeitrag(\"Titel\")\r\nDebug.Print<\/span> \"Ausgabe:\", colBeitrag(\"Ausgabe\")\r\nDebug.Print<\/span> \"Autor:\", colBeitrag(\"Autor\")(\"Vorname\"), colBeitrag(\"Autor\")(\"Nachname\")<\/pre>\nDim <\/span>col As <\/span>Collection\r\nSet<\/span> col = New<\/span> Collection<\/pre>\ncol.Add \"André\", \"Vorname\"\r\ncol.Add \"Minhorst\", \"Nachname\"<\/pre>\n
Debug.Print<\/span> col!Vorname, col!Nachname\r\nDebug.Print<\/span> col(\"Vorname\"), col(\"Nachname\")<\/pre>\nDim <\/span>colPerson As <\/span>Collection\r\nDim <\/span>colHobbys As <\/span>Collection\r\nDim <\/span>i As Integer<\/span>\r\nSet<\/span> colPerson = New<\/span> Collection\r\ncolPerson.Add \"André\", \"Vorname\"\r\ncolPerson.Add \"Minhorst\", \"Nachname\"\r\nSet<\/span> colHobbys = New<\/span> Collection\r\nWith<\/span> colHobbys\r\n .Add \"Lesen\", \"Hobby1\"\r\n .Add \"Reiten\", \"Hobby2\"\r\n .Add \"Programmieren\", \"Hobby3\"\r\nEnd With<\/span>\r\ncolPerson.Add colHobbys, \"Hobbys\"\r\nDebug.Print<\/span> colPerson!Vorname, colPerson!Nachname\r\nDebug.Print<\/span> colPerson!Hobbys!Hobby1\r\nDebug.Print<\/span> colPerson!Hobbys!Hobby2\r\nDebug.Print<\/span> colPerson!Hobbys!Hobby3\r\nFor i = 1 To 3\r\n Debug.Print<\/span> _\r\n colPerson!Hobbys(\"Hobby\" & i)\r\nNext<\/span> i\r\nFor i = 1 To 3\r\n Debug.Print<\/span> colPerson!Hobbys(i)\r\nNext<\/span> i<\/pre>\nPublic Function <\/span>JSONParsen(strJSON As String<\/span>) As <\/span>Collection\r\n Dim <\/span>l As Long<\/span>\r\n strJSON = SteuerzeichenUndLeerzeichenEntfernen(strJSON)\r\n l = 1\r\n Select Case Mid<\/span>(strJSON, l, 1)\r\n Case <\/span>\"{\"\r\n Set<\/span> JSONParsen = NameWertParsen(strJSON, l)\r\n Case <\/span>\"[\"\r\n Set<\/span> JSONParsen = ListeParsen(strJSON, 1)\r\n End Select<\/span>\r\nEnd Function<\/span><\/pre>\nPublic Function <\/span>SteuerzeichenUndLeerzeichenEntfernen(strJSON As String<\/span>) As String<\/span>\r\n Dim <\/span>l As Long<\/span>\r\n Dim <\/span>strTemp As String<\/span>\r\n Dim <\/span>bolAnfuehrungszeichen As Boolean<\/span>\r\n Dim <\/span>strAktuell As String<\/span>\r\n For l = 1 To Len<\/span>(strJSON)\r\n strAktuell = Mid<\/span>(strJSON, l, 1)\r\n If <\/span>strAktuell = \"\"\"\" Then<\/span>\r\n bolAnfuehrungszeichen = Not<\/span> bolAnfuehrungszeichen\r\n End If<\/span>\r\n If <\/span>Not<\/span> IstSteuerzeichen(strAktuell) Then<\/span>\r\n If <\/span>strAktuell = \" \" Then<\/span>\r\n If <\/span>bolAnfuehrungszeichen Then<\/span>\r\n strTemp = strTemp & strAktuell\r\n End If<\/span>\r\n Else<\/span>\r\n strTemp = strTemp & strAktuell\r\n End If<\/span>\r\n End If<\/span>\r\n Next<\/span> l\r\n SteuerzeichenUndLeerzeichenEntfernen = strTemp\r\nEnd Function<\/span><\/pre>\nPublic Function <\/span>IstSteuerzeichen(strZeichen As String<\/span>) As Boolean<\/span>\r\n Select Case strZeichen\r\n Case <\/span>vbCrLf<\/span>, vbCr<\/span>, vbLf<\/span>, vbTab\r\n IstSteuerzeichen = True<\/span>\r\n End Select<\/span>\r\nEnd Function<\/span><\/pre>\n\n
![\"Ein](\"..\/fileadmin\/_temp_\/2013_04\/pic_893_001.png\")
<\/p>\n{\"Beispielname\":\"Beispielwert\"}<\/pre>\nPublic Function <\/span>NameWertParsen(strJSON As String<\/span>, l As Long<\/span>, _\r\n Optional<\/span> strSchluesselParent As String<\/span>) As <\/span>Collection\r\n Dim <\/span>col As <\/span>Collection\r\n Dim <\/span>strSchluessel As String<\/span>\r\n Set<\/span> col = New<\/span> Collection\r\n Do While<\/span> Not<\/span> Mid<\/span>(strJSON, l, 1) = \"}\"\r\n l = l + 1\r\n If <\/span>Mid<\/span>(strJSON, l, 1) = \",\" Then<\/span>\r\n l = l + 1\r\n End If<\/span>\r\n strSchluessel = SchluesselParsen(strJSON, l)\r\n col.Add WertParsen(strJSON, l, strSchluesselParent & \"!\" _\r\n & strSchluessel), strSchluessel\r\n Loop<\/span>\r\n l = l + 1\r\n Set<\/span> NameWertParsen = col\r\nEnd Function<\/span><\/pre>\nPrivate Function <\/span>SchluesselParsen(strJSON As String<\/span>, l As Long<\/span>) As String<\/span>\r\n Dim <\/span>strSchluessel As String<\/span>\r\n Dim <\/span>bolAnfuehrungszeichen As Boolean<\/span>\r\n Do While<\/span> l > 0 And l <= Len<\/span>(strJSON)\r\n Select Case Mid<\/span>(strJSON, l, 1)\r\n Case <\/span>\"\"\"\"\r\n bolAnfuehrungszeichen = Not<\/span> bolAnfuehrungszeichen\r\n l = l + 1\r\n Case <\/span>\":\"\r\n If <\/span>Not<\/span> bolAnfuehrungszeichen Then<\/span> \r\n l = l + 1\r\n Exit Do<\/span>\r\n End If<\/span>\r\n Case Else<\/span>\r\n strSchluessel = strSchluessel & Mid<\/span>(strJSON, l, 1)\r\n l = l + 1\r\n End Select<\/span>\r\n Loop<\/span>\r\n SchluesselParsen = strSchluessel\r\nEnd Function<\/span><\/pre>\n\n
col.Add WertParsen(strJSON, l, strSchluesselParent & \"!\" & strSchluessel), strSchluessel<\/pre>\n
Private Function <\/span>WertParsen(strJSON As String<\/span>, l As Long<\/span>, _\r\n Optional<\/span> strSchluessel As String<\/span>) As Variant<\/span>\r\n Select Case Mid<\/span>(strJSON, l, 1)\r\n Case <\/span>\"\"\"\", \"''\"\r\n WertParsen = ZeichenketteParsen(strJSON, l)\r\n