PLZ-Lookup per Rest-API

Im Grundlagenartikel zu Rest-APIs haben wir gezeigt, wie das Konzept funktioniert und wie eine wiederverwendbare HTTPRequest-Funktion aussieht. Jetzt setzen wir das Gelernte das erste Mal gegen eine echte API ein: Wir fragen zu einer deutschen Postleitzahl den zugehörigen Ort und die Koordinaten ab – ohne Anmeldung, ohne API-Key und vollkommen kostenlos. Nebenbei lernen wir, wie wir die JSON-Antwort mit wenigen Handgriffen auslesen.

Vorbereitungen

Um die nachfolgenden Beispiele auszuprobieren, benötigst Du die beiden in der Beispieldatatenbank enthaltenen Module mdlJSON und mdlJSONDOM.

Außerdem müssen wir zwei Verweise zu Bibliotheken hinzufügen, was wir über das Verweise-Fenster des VBA-Editors erledigen.

Hier fügen wir die beiden Verweise auf die Bibliotheken Microsoft Scripting Runtime und Microsoft XML, v6.0 hinzu (siehe Bild 1).

Bild 1: Hinzufügen der benötigten Verweise

Die API: Zippopotam.us

Der Dienst Zippopotam.us ist eine schlanke, öffentlich zugängliche REST-API für Postleitzahlen aus über 60 Ländern. Es gibt kein Entwicklerkonto, keinen API-Key und keine Ratenbegrenzung, die für unsere Zwecke relevant wäre.

Ein einziger GET-Aufruf genügt, um zu einer Postleitzahl den Ort und die geografischen Koordinaten zu erhalten.

Die URL hat immer denselben Aufbau:

https://api.zippopotam.us/<Länderkürzel>/<PLZ>

Für die Postleitzahl 44787 in Deutschland sieht der Aufruf also so aus:

https://api.zippopotam.us/de/44787

Das Länderkürzel entspricht dem zweistelligen ISO-3166-Code in Kleinbuchstaben, also zum Beispiel de für Deutschland, at für Österreich oder ch für die Schweiz.

Die JSON-Antwort verstehen

Wenn wir die URL im Browser aufrufen, erhalten wir eine JSON-Antwort, die in etwa so aussieht:

{
   "post code": "44787",
   "country": "Germany",
   "country abbreviation": "DE",
   "places": [
     {
       "place name": "Bochum",
       "longitude": "7.2167",
       "state": "North Rhine-Westphalia",
       "state abbreviation": "NW",
       "latitude": "51.4833"
     }
   ]
}

Die Struktur ist flach: Außen liegen einfache Felder wie post code und country. Das Feld places ist ein Array – erkennbar an den eckigen Klammern -, das ein oder mehrere Objekte enthalten kann.

In Deutschland ist einer PLZ nicht zwingend genau ein Ort zugeordnet – das Array kann also mehrere Elemente enthalten. Wie wir damit umgehen, zeigt die Funktion PLZLookup weiter unten.

JSON-Antworten auslesen mit ParseJson

VBA kennt JSON nicht von Natur aus. Um auf die einzelnen Felder zuzugreifen, verwenden wir die Funktion ParseJson aus dem Modul mdlJSON. Diese wandelt die JSON-Zeichenkette in ein Objektmodell aus Dictionary– und Collection-Elementen um.

Auf die einzelnen Werte greifen wir dann über verkettete Item-Aufrufe zu. Die vollständige Beschreibung dieser Module findest Du in den Artikeln Mit JSON arbeiten (www.vbentwickler.de/361) und JSON-Dokumente per Objektmodell zusammenstellen (www.vbentwickler.de/412).

Damit wir aber gar nicht erst rätselmüßig blättern müssen, gibt es den praktischen Helfer GetJSONDOM aus dem Modul mdlJSONDOM. Dieser nimmt die JSON-Zeichenkette entgegen und gibt für jedes enthaltene Feld genau den VBA-Ausdruck aus, mit dem wir auf den Wert zugreifen.

Diese Prozedur können wir ganz einfach wie folgt gestalten:

Public Sub JSONDOMAnzeigen()
     Dim strURL As String
     Dim strResponse As String
     Dim intStatus As Integer
     strURL = "https://api.zippopotam.us/de/44787"
     intStatus = HTTPRequest(strURL, "GET", strResponse)
     If intStatus = 200 Then
         Debug.Print GetJSONDOM(strResponse, True)
     End If
End Sub

Die Prozedur führt den Aufruf der Rest-API aus und bekommt das Ergebnis im Rückgabeparameter strResponse. Für unsere PLZ-Antwort liefert GetJSONDOM im Direktbereich folgendes Ergebnis:

objJSON.Item("post code"): 44787
objJSON.Item("country"): Germany
objJSON.Item("country abbreviation"): DE
objJSON.Item("places").Item(1)
     .Item("place name"): Bochum
objJSON.Item("places").Item(1)
     .Item("longitude"): 7.2167
objJSON.Item("places").Item(1)
     .Item("state"): North Rhine-Westphalia
objJSON.Item("places").Item(1)
     .Item("latitude"): 51.4833

Wir sehen sofort: Die einfachen Felder sprechen wir direkt mit objJSON.Item(“Feldname”) an. Das verschachtelte Feld places erfordert einen zusätzlichen Item(1)-Aufruf für das erste Element des Arrays – und dahinter nochmals Item(“Feldname”) für das gewünschte Unterfeld (siehe Bild 2).

Bild 2: Ausgabe von GetJSONDOM im Direktbereich des VBA-Editors

Wenn wir beispielsweise mehrere Elemente für places zurückbekommen würden, könnten wir diese in einer For…Next-Schleife durchlaufen.

Damit brauchen wir für keine JSON-Antwort mehr zu rätseln. Wir rufen die API einmal testweise auf, geben das Ergebnis durch GetJSONDOM und haben sofort alle benötigten Zugriffspfade schwarz auf weiß.

https://api.zippopotam.us/<Länderkürzel>/<PLZ>

https://api.zippopotam.us/de/44787

{
   "post code": "44787",
   "country": "Germany",
   "country abbreviation": "DE",
   "places": [
     {
       "place name": "Bochum",
       "longitude": "7.2167",
       "state": "North Rhine-Westphalia",
       "state abbreviation": "NW",
       "latitude": "51.4833"
     }
   ]
}

Public Sub JSONDOMAnzeigen()
     Dim strURL As String
     Dim strResponse As String
     Dim intStatus As Integer
     strURL = "https://api.zippopotam.us/de/44787"
     intStatus = HTTPRequest(strURL, "GET", strResponse)
     If intStatus = 200 Then
         Debug.Print GetJSONDOM(strResponse, True)
     End If
End Sub

objJSON.Item("post code"): 44787
objJSON.Item("country"): Germany
objJSON.Item("country abbreviation"): DE
objJSON.Item("places").Item(1)
     .Item("place name"): Bochum
objJSON.Item("places").Item(1)
     .Item("longitude"): 7.2167
objJSON.Item("places").Item(1)
     .Item("state"): North Rhine-Westphalia
objJSON.Item("places").Item(1)
     .Item("latitude"): 51.4833

Oder hast Du eine konkrete Frage zu Deiner eigenen Access-Anwendung?

Vielleicht stellt Deine Anwendung Dich vor eine Herausforderung, zu der Du bisher keine Lösung findest. Schlechte Performance, kein ausreichender Zugriffsschutz, Du bist unsicher über Dein Datenmodell oder Dein Code liefert unerklärliche Fehler?

In unserem kostenlosen Access-Audit schaut sich André Minhorst persönlich gemeinsam mit Dir Deine Lösung per Zoom an – und zeigt Dir, wo Datenmodell, VBA-Code, Ergonomie und Sicherheit Optimierungspotenzial bieten.