{"id":55000507,"date":"2026-04-01T00:00:00","date_gmt":"2026-05-16T18:26:28","guid":{"rendered":"http:\/\/access-im-unternehmen.aix-dev.de\/aiu\/?p=507"},"modified":"-0001-11-30T00:00:00","modified_gmt":"-0001-11-30T00:00:00","slug":"PLZLookup_per_RestAPI","status":"publish","type":"post","link":"https:\/\/vbentwickler.de\/PLZLookup_per_RestAPI\/","title":{"rendered":"PLZ-Lookup per Rest-API"},"content":{"rendered":"

\"\"Im Grundlagenartikel zu Rest-APIs haben wir gezeigt, wie das Konzept funktioniert und wie eine wiederverwendbare HTTPRequest<\/b>-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.<\/b><\/p>\n

Vorbereitungen<\/h2>\n

Um die nachfolgenden Beispiele auszuprobieren, benötigst Du die beiden in der Beispieldatatenbank enthaltenen Module mdlJSON <\/b>und mdlJSONDOM<\/b>.<\/p>\n

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

Hier fügen wir die beiden Verweise auf die Bibliotheken Microsoft Scripting Runtime <\/b>und Microsoft XML, v6.0 <\/b>hinzu (siehe Bild 1).<\/p>\n

\"Hinzufügen<\/p>\n

Bild 1: Hinzufügen der benötigten Verweise<\/span><\/b><\/p>\n

Die API: Zippopotam.us<\/h2>\n

Der Dienst Zippopotam.us<\/b> 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.<\/p>\n

Ein einziger GET-Aufruf genügt, um zu einer Postleitzahl den Ort und die geografischen Koordinaten zu erhalten.<\/p>\n

Die URL hat immer denselben Aufbau:<\/p>\n

https:\/\/api.zippopotam.us\/<Länderkürzel>\/<PLZ><\/pre>\n
Für die Postleitzahl 44787 in Deutschland sieht der Aufruf also so aus:<\/p>\n
https:\/\/api.zippopotam.us\/de\/44787<\/pre>\n
Das Länderkürzel entspricht dem zweistelligen ISO-3166-Code in Kleinbuchstaben, also zum Beispiel de<\/b> für Deutschland, at<\/b> für Österreich oder ch<\/b> für die Schweiz.<\/p>\n
Die JSON-Antwort verstehen<\/h2>\n
Wenn wir die URL im Browser aufrufen, erhalten wir eine JSON-Antwort, die in etwa so aussieht:<\/p>\n
{\r\n   \"post code\": \"44787\",\r\n   \"country\": \"Germany\",\r\n   \"country abbreviation\": \"DE\",\r\n   \"places\": [\r\n     {\r\n       \"place name\": \"Bochum\",\r\n       \"longitude\": \"7.2167\",\r\n       \"state\": \"North Rhine-Westphalia\",\r\n       \"state abbreviation\": \"NW\",\r\n       \"latitude\": \"51.4833\"\r\n     }\r\n   ]\r\n}<\/pre>\n
Die Struktur ist flach: Außen liegen einfache Felder wie post code<\/b> und country<\/b>. Das Feld places<\/b> ist ein Array – erkennbar an den eckigen Klammern -, das ein oder mehrere Objekte enthalten kann.<\/p>\n
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<\/b> weiter unten.<\/p>\n
JSON-Antworten auslesen mit ParseJson<\/h2>\n
VBA kennt JSON nicht von Natur aus. Um auf die einzelnen Felder zuzugreifen, verwenden wir die Funktion ParseJson<\/b> aus dem Modul mdlJSON<\/b>. Diese wandelt die JSON-Zeichenkette in ein Objektmodell aus Dictionary<\/b>– und Collection<\/b>-Elementen um.<\/p>\n
Auf die einzelnen Werte greifen wir dann über verkettete Item<\/b>-Aufrufe zu. Die vollständige Beschreibung dieser Module findest Du in den Artikeln Mit JSON arbeiten<\/b> (www.vbentwickler.de\/361<\/b>) und JSON-Dokumente per Objektmodell zusammenstellen<\/b> (www.vbentwickler.de\/412<\/b>).<\/p>\n
Damit wir aber gar nicht erst rätselmüßig blättern müssen, gibt es den praktischen Helfer GetJSONDOM<\/b> aus dem Modul mdlJSONDOM<\/b>. 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.<\/p>\n
Diese Prozedur können wir ganz einfach wie folgt gestalten:<\/p>\n
Public Sub <\/span>JSONDOMAnzeigen()\r\n     Dim <\/span>strURL As String<\/span>\r\n     Dim <\/span>strResponse As String<\/span>\r\n     Dim <\/span>intStatus As Integer<\/span>\r\n     strURL = \"https:\/\/api.zippopotam.us\/de\/44787\"\r\n     intStatus = HTTPRequest(strURL, \"GET\", strResponse)\r\n     If <\/span>intStatus = 200 Then<\/span>\r\n         Debug.Print<\/span> GetJSONDOM(strResponse, True<\/span>)\r\n     End If<\/span>\r\nEnd Sub<\/span><\/pre>\n
Die Prozedur führt den Aufruf der Rest-API aus und bekommt das Ergebnis im Rückgabeparameter strResponse<\/b>. Für unsere PLZ-Antwort liefert GetJSONDOM<\/b> im Direktbereich folgendes Ergebnis:<\/p>\n
objJSON.Item(\"post code\"): 44787\r\nobjJSON.Item(\"country\"): Germany\r\nobjJSON.Item(\"country abbreviation\"): DE\r\nobjJSON.Item(\"places\").Item(1)\r\n     .Item(\"place name\"): Bochum\r\nobjJSON.Item(\"places\").Item(1)\r\n     .Item(\"longitude\"): 7.2167\r\nobjJSON.Item(\"places\").Item(1)\r\n     .Item(\"state\"): North Rhine-Westphalia\r\nobjJSON.Item(\"places\").Item(1)\r\n     .Item(\"latitude\"): 51.4833<\/pre>\n
Wir sehen sofort: Die einfachen Felder sprechen wir direkt mit objJSON.Item(“Feldname”)<\/b> an. Das verschachtelte Feld places<\/b> erfordert einen zusätzlichen Item(1)<\/b>-Aufruf für das erste Element des Arrays – und dahinter nochmals Item(“Feldname”)<\/b> für das gewünschte Unterfeld (siehe Bild 2).<\/p>\n
\"Ausgabe<\/p>\n
Bild 2: Ausgabe von GetJSONDOM im Direktbereich des VBA-Editors<\/span><\/b><\/p>\n
Wenn wir beispielsweise mehrere Elemente für places zurückbekommen würden, könnten wir diese in einer For…Next<\/b>-Schleife durchlaufen.<\/p>\n
Damit brauchen wir für keine JSON-Antwort mehr zu rätseln. Wir rufen die API einmal testweise auf, geben das Ergebnis durch GetJSONDOM<\/b> und haben sofort alle benötigten Zugriffspfade schwarz auf weiß.<\/p>\n
Beispieldatenbank<\/h2>\n
Als Basis dient eine einfache Access-Datenbank mit einer Tabelle namens tblAdressen<\/b>. Die Tabelle enthält die Felder ID<\/b> (Autowert), Name<\/b>, Strasse<\/b>, PLZ<\/b>, Ort<\/b>, Bundesland<\/b>, Breitengrad<\/b> und Laengengrad<\/b>.<\/p>\n
Die letzten vier Felder sollen per API befüllt werden, sobald eine neue PLZ eingetragen wird.<\/p>\n
Die Funktion HTTPRequest<\/b> aus dem Grundlagenartikel legen wir in einem eigenen Modul namens mdlHTTP<\/b> ab.<\/p>\n
PLZ-Lookup: Die Kernfunktion<\/h2>\n
Die Funktion PLZLookup<\/b> nimmt eine Postleitzahl und ein optionales Länderkürzel entgegen und gibt ein Dictionary<\/b>-Objekt mit den Ergebnisfeldern zurück. Enthält die Antwort mehrere Orte für dieselbe PLZ, zeigt die Funktion eine InputBox<\/b> an, in der der Benutzer den gewünschten Ort auswählen kann. Liefert die API keinen Treffer, gibt die Funktion Nothing<\/b> zurück. Listing 1 zeigt die vollständige Implementierung.<\/p>\n
Public Function <\/span>PLZLookup(strPLZ As String<\/span>, Optional<\/span> strLand As String<\/span> = \"de\") As Object<\/span>\r\n     Dim <\/span>strURL As String<\/span>, strResponse As String<\/span>\r\n     Dim <\/span>intStatus As Integer<\/span>\r\n     Dim <\/span>objJson As Object<\/span>, objPlaces As Object<\/span>\r\n     Dim <\/span>dicErgebnis As <\/span>New<\/span> dictionary\r\n     Dim <\/span>intIndex As Integer<\/span>\r\n     Dim <\/span>strAuswahl As String<\/span>, strListe As String<\/span>\r\n     Dim <\/span>i As Integer<\/span>\r\n     strURL = \"https:\/\/api.zippopotam.us\/\" & strLand & \"\/\" & strPLZ\r\n     intStatus = HTTPRequest(strURL, \"GET\", strResponse)\r\n     Select Case intStatus\r\n         Case <\/span>200\r\n             Set<\/span> objJson = ParseJson(strResponse)\r\n             Set<\/span> objPlaces = objJson.Item(\"places\")\r\n             If <\/span>objPlaces.Count > 1 Then<\/span>\r\n                 'Mehrere Orte: Auswahl anzeigen\r\n                 strListe = \"Es gibt mehrere Orte\" & \" für die PLZ \" & strPLZ & \":\" & vbCrLf<\/span>\r\n                 For i = 1 To objPlaces.Count\r\n                     strListe = strListe & \"[\" & i & \"] \" & objPlaces.Item(i).Item(\"place name\") & vbCrLf<\/span>\r\n                 Next<\/span> i\r\n                 strListe = strListe & \"Wähle den gewünschten\" & \" Ort aus (1-\" & objPlaces.Count & \"):\"\r\n                 strAuswahl = InputBox(strListe, \"PLZ-Auswahl\", \"1\")\r\n                 If <\/span>strAuswahl = \"\" Then<\/span>\r\n                     'Abbruch durch Benutzer\r\n                     Set<\/span> PLZLookup = Nothing\r\n                     Exit Function<\/span>\r\n                 End If<\/span>\r\n                 intIndex = Val(strAuswahl)\r\n                 If <\/span>intIndex < 1 Or intIndex > objPlaces.Count Then<\/span>\r\n                     intIndex = 1\r\n                 End If<\/span>\r\n             Else<\/span>\r\n                 intIndex = 1\r\n             End If<\/span>\r\n             dicErgebnis.Add \"Ort\", objPlaces.Item(intIndex).Item(\"place name\")\r\n             dicErgebnis.Add \"Bundesland\", objPlaces.Item(intIndex).Item(\"state\")\r\n             dicErgebnis.Add \"Breitengrad\", objPlaces.Item(intIndex).Item(\"latitude\")\r\n             dicErgebnis.Add \"Laengengrad\", objPlaces.Item(intIndex).Item(\"longitude\")\r\n             Set<\/span> PLZLookup = dicErgebnis\r\n         Case <\/span>404\r\n             'PLZ nicht gefunden\r\n             Set<\/span> PLZLookup = Nothing\r\n         Case Else<\/span>\r\n             Set<\/span> PLZLookup = Nothing\r\n     End Select<\/span>\r\nEnd Function<\/span><\/pre>\n
Listing 1: Die Funktion PLZLookup ruft die API auf und zeigt bei mehreren Orten eine Auswahlbox an.<\/span><\/b><\/p>\n
Die Funktion baut zunächst die URL zusammen und übergibt sie an HTTPRequest<\/b>. Im Erfolgsfall parst sie die Antwort mit ParseJson<\/b> und legt das places<\/b>-Array in der Variable objPlaces<\/b> ab.<\/p>\n
Enthält dieses mehr als ein Element, stellt die Funktion eine nummerierte Liste aller Ortsnamen zusammen und zeigt diese in einer InputBox<\/b> an. Der Benutzer gibt die gewünschte Nummer ein – bei ungültiger Eingabe wird automatisch der erste Eintrag verwendet, bei Abbruch gibt die Funktion Nothing<\/b> zurück.<\/p>\n
Enthält das Array nur ein Element, wird intIndex<\/b> direkt auf 1 gesetzt. Anschließend füllt die Funktion das Dictionary<\/b> mit den vier Feldern des gewählten Eintrags.<\/p>\n
Das Dictionary enthält dann die Elemente Ort<\/b>, Bundesland<\/b>, Breitengrad <\/b>und Längengrad<\/b>.<\/p>\n
Ein Beispielaufruf sieht wie folgt aus. Hier holen wir das Dictionary<\/b>-Objekt mit dem Ergebnis und geben den Wert des Eintrags Ort <\/b>aus:<\/p>\n
Public Sub <\/span>Test_PLZLookup()\r\n     Dim <\/span>dic As <\/span>Scripting.Dictionary\r\n     Set<\/span> dic = PLZLookup(\"47137\")\r\n     Debug.Print<\/span> dic(\"Ort\")\r\nEnd Sub<\/span><\/pre>\n
Einzelnen Datensatz aktualisieren<\/h2>\n
Mit PLZLookup<\/b> im Gepäck ist die Prozedur zum Aktualisieren eines Datensatzes überschaubar. Die Prozedur AdresseAktualisieren<\/b> in Listing 2 nimmt eine PLZ entgegen, ruft die Funktion auf und schreibt die Ergebnisse per DAO in die Tabelle.<\/p>\n
Public Sub <\/span>AdresseAktualisieren(strPLZ As String<\/span>)\r\n     Dim <\/span>db As <\/span>DAO.Database\r\n     Dim <\/span>rst As <\/span>DAO.Recordset\r\n     Dim <\/span>dicErgebnis As Object<\/span>\r\n     Set<\/span> dicErgebnis = PLZLookup(strPLZ)\r\n     If <\/span>dicErgebnis Is Nothing Then<\/span>\r\n         MsgBox<\/span> \"PLZ \" & strPLZ & \" nicht gefunden.\"\r\n         Exit Sub<\/span>\r\n     End If<\/span>\r\n     Set<\/span> db = CurrentDb\r\n     Set<\/span> rst = db.OpenRecordset(\"SELECT * FROM tblAdressen WHERE PLZ = '\" & strPLZ & \"'\", dbOpenDynaset)\r\n     Do While<\/span> Not<\/span> rst.EOF\r\n         rst.Edit\r\n         rst!Ort = dicErgebnis.Item(\"Ort\")\r\n         rst!Bundesland = dicErgebnis.Item(\"Bundesland\")\r\n         rst!Breitengrad = dicErgebnis.Item(\"Breitengrad\")\r\n         rst!Laengengrad = dicErgebnis.Item(\"Laengengrad\")\r\n         rst.Update\r\n         rst.MoveNext<\/span>\r\n     Loop<\/span>\r\n     rst.Close\r\n     Set<\/span> rst = Nothing\r\n     Set<\/span> db = Nothing\r\nEnd Sub<\/span><\/pre>\n
Listing 2: Prozedur zum Aktualisieren aller Datensätze mit einer bestimmten PLZ<\/span><\/b><\/p>\n
Die Prozedur öffnet ein Recordset mit allen Datensätzen, deren PLZ<\/b>-Feld den gesuchten Wert enthält, und schreibt die vier Felder in einer Schleife in jeden Treffer. Damit werden auch mehrere Datensätze mit derselben PLZ in einem Rutsch aktualisiert.<\/p>\n
Alle leeren Datensätze auf einmal aktualisieren<\/h2>\n
In der Praxis möchte man nicht jede PLZ einzeln aufrufen, sondern alle Datensätze, bei denen der Ort noch fehlt, in einem Durchlauf ergänzen. Listing 3 zeigt, wie das gelingt.<\/p>\n
Public Sub <\/span>AlleAktualisieren()\r\n     Dim <\/span>db As <\/span>DAO.Database\r\n     Dim <\/span>rst As <\/span>DAO.Recordset\r\n     Dim <\/span>dicErgebnis As Object<\/span>\r\n     Dim <\/span>strLetztePLZ As String<\/span>\r\n     Set<\/span> db = CurrentDb\r\n     Set<\/span> rst = db.OpenRecordset(\"SELECT * FROM tblAdressen WHERE Ort IS NULL ORDER BY PLZ\", dbOpenDynaset)\r\n     Do While<\/span> Not<\/span> rst.EOF\r\n         If <\/span>rst!PLZ <> strLetztePLZ Then<\/span>\r\n             strLetztePLZ = rst!PLZ\r\n             Set<\/span> dicErgebnis = PLZLookup(strLetztePLZ)\r\n         End If<\/span>\r\n         If <\/span>Not<\/span> dicErgebnis Is Nothing Then<\/span>\r\n             rst.Edit\r\n             rst!Ort = dicErgebnis.Item(\"Ort\")\r\n             rst!Bundesland = dicErgebnis.Item(\"Bundesland\")\r\n             rst!Breitengrad = dicErgebnis.Item(\"Breitengrad\")\r\n             rst!Laengengrad = dicErgebnis.Item(\"Laengengrad\")\r\n             rst.Update\r\n         End If<\/span>\r\n         rst.MoveNext<\/span>\r\n     Loop<\/span>\r\n     rst.Close\r\n     Set<\/span> rst = Nothing\r\n     Set<\/span> db = Nothing\r\n     MsgBox<\/span> \"Fertig.\"\r\nEnd Sub<\/span><\/pre>\n
Listing 3: Alle Datensätze ohne Ort in einem Durchlauf aktualisieren.<\/span><\/b><\/p>\n
Die Prozedur liest alle Datensätze, bei denen Ort<\/b> leer ist, nach PLZ sortiert ein. Die Variable strLetztePLZ<\/b> merkt sich die zuletzt abgefragte PLZ. Taucht dieselbe PLZ mehrfach auf, wird die API nicht erneut aufgerufen – das Ergebnis aus dicErgebnis<\/b> liegt noch vor und wird direkt wiederverwendet.<\/p>\n
Dadurch schonen wir die API und verringern die Laufzeit spürbar.<\/p>\n
Einschub: Ungültige PLZ abfangen<\/h2>\n
Gibt der Benutzer eine PLZ ein, die die API nicht kennt, antwortet der Dienst mit dem Statuscode 404.<\/p>\n
PLZLookup<\/b> gibt in diesem Fall Nothing<\/b> zurück, und die aufrufende Prozedur prüft darauf mit If dicErgebnis Is Nothing<\/b>. Das ist die korrekte Methode, um auf ein nicht gesetztes Objekt zu prüfen – ein Vergleich mit = Nothing<\/b> funktioniert in VBA nicht.<\/p>\n
Zusammenfassung und Ausblick<\/h2>\n
Dieser Artikel hat gezeigt, wie wir mit wenigen Zeilen VBA-Code eine öffentliche Rest-API ohne jede Registrierung ansprechen, die JSON-Antwort mit ParseJson<\/b> auslesen und die Ergebnisse direkt in eine Access-Tabelle schreiben. Der Trick mit strLetztePLZ<\/b> zeigt dabei, wie man unnötige API-Aufrufe vermeidet.<\/p>\n
Im nächsten Artikel greifen wir auf eine API zu, die eine Authentifizierung erfordert. Wir zeigen, wie wir einen API-Key sicher in der Windows-Registry ablegen und ihn beim Aufruf als Authorization<\/b>-Header übergeben.<\/p>\n","protected":false},"excerpt":{"rendered":"
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.<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"om_disable_all_campaigns":false,"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"_uf_show_specific_survey":0,"_uf_disable_surveys":false,"footnotes":""},"categories":[66022026,662026,44000012],"tags":[],"yst_prominent_words":[],"class_list":["post-55000507","post","type-post","status-publish","format-standard","hentry","category-66022026","category-662026","category-Interaktiv"],"aioseo_notices":[],"_links":{"self":[{"href":"https:\/\/vbentwickler.de\/data\/wp\/v2\/posts\/55000507","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/vbentwickler.de\/data\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/vbentwickler.de\/data\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/vbentwickler.de\/data\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/vbentwickler.de\/data\/wp\/v2\/comments?post=55000507"}],"version-history":[{"count":0,"href":"https:\/\/vbentwickler.de\/data\/wp\/v2\/posts\/55000507\/revisions"}],"wp:attachment":[{"href":"https:\/\/vbentwickler.de\/data\/wp\/v2\/media?parent=55000507"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/vbentwickler.de\/data\/wp\/v2\/categories?post=55000507"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/vbentwickler.de\/data\/wp\/v2\/tags?post=55000507"},{"taxonomy":"yst_prominent_words","embeddable":true,"href":"https:\/\/vbentwickler.de\/data\/wp\/v2\/yst_prominent_words?post=55000507"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}