Lies diesen Artikel und viele weitere mit einem kostenlosen, einwöchigen Testzugang.
Fast jede Datenbankanwendung speichert Adressen in irgendeiner Form – seien es Kunden-, Mitarbeiter-, Privat- oder Firmenadressen. Diese gelangen über irgendeine Schnittstelle in die Datenbank, beispielsweise über den Import von Daten aus externen Dateien oder auch durch manuelle Eingabe. Aber prüfen Sie diese Adressen auch Oder lassen Sie es darauf ankommen, bis die Post einen Brief zu Ihnen zurückschickt, weil sie mit der Adresse nichts anfangen kann. In diesem Beitrag zeigen wir Ihnen, wie Sie fehlerhafte und unvollständige Adressen aufspüren und sie gegebenenfalls sogar automatisch korrigieren und ergänzen.
Damit Sie später nicht enttäuscht sind: Die hier vorgestellte Lösung kümmert sich tatsächlich nur um Adressen, also um Straßen, Postleitzahlen, Orte, Bundesländer und Länder. Sie prüft nicht, ob eine bestimmte Person einer Adresse zugeordnet werden kann oder nicht – für einen Datenbestand, der dies erledigt, müssten Sie mit Sicherheit eine Menge Geld hinblättern.
Unsere Lösung ist kostenlos und erledigt immerhin die Kontrolle und gegebenenfalls die Ergänzung von Adressen wie etwa Borkhofer Str. 17, Duisburg.
Wenn sie eine passende Adresse findet, liefert sie eine eventuell korrigierte Version zurück (Str. wird beispielsweise zu Straße) und ergänzt die Adresse um fehlende Elemente, hier etwa Postleitzahl, Land und Bundesland. Und falls Sie Bedarf haben, erhalten Sie gleichzeitig noch die Geodaten der angegebenen Adresse.
Wenn tatsächlich keine passende Adresse zu finden ist, soll die Lösung dies melden – so können Sie sich, soweit vorhanden, via E-Mail oder Telefon an den Adressaten wenden und die korrekte Adresse ermitteln. Vielleicht stellt sich ja dabei heraus, dass es die angegebene Adresse tatsächlich nicht gibt und sich jemand einen schlechten Scherz erlaubt hat.
Google als Datenlieferant
Der Kern dieser Anwendung ist ein Dienst von Google, nämlich die Google Geocoding API (http://code.google.com/intl/de/apis/maps/documentation/geocoding/).
Wenn Sie diesem unter einer bestimmten Adresse die richtigen Parameter unterjubeln, liefert er ein XML-Dokument mit den gewünschten Informationen zurück. Um beim Beispiel Borkhofer Str. 17, Duisburg zu bleiben, das immerhin die Postleitzahl vermissen lässt, geben Sie einfach einmal die folgende URL im Webbrowser Ihrer Wahl ein:
http://maps.google.com/maps/api/geocode/xmladdress=Borkhofer+Str.,Duisburg&sensor=false&language=de
Die Bestandteile sind schnell erklärt: http://maps.google.com/maps/api/geocode/ ist die eigentliche Webadresse des Services. xml gibt an, dass Google das Ergebnis im XML-Format liefern soll – alternativ können Sie hier json für die JavaScript Object Notation angeben. Schließlich folgen noch weitere Parameter, die durch ein Fragezeichen eingeleitet werden: address erwartet den eigentlichen Adress-Ausdruck.
Dessen Elemente trennen Sie durch Kommata voneinander. Leerzeichen ersetzen Sie durch das Plus-Zeichen:
adress=Borkhofer+Str.,Duisburg
Weitere Parameter werden durch das Kaufmanns-Und eingeleitet. Der folgende Ausdruck gibt an, dass die Anfrage von einem Gerät ohne Positionsermittlung kommt:
&sensor=false
Der letzte Ausdruck gibt schließlich die Sprache an, in der Google die Antwort liefern soll:
&language=de
Beim Absetzen der Abfrage im Browser kann dieser Parameter wegfallen, da er normalerweise automatisch die Länderkennung auswirft, später beim Aufruf per VBA müssen Sie die Länderkennung jedoch mitschicken.
Als Antwort erhalten Sie eine XML-Datei wie in Bild 1.
Bild 1: Diese Antwort liefert eine Adress-Anfrage via Browser.
XML-Adressdaten per Code holen
Natürlich sollen Sie Ihre Adressdaten nicht alle per Webbrowser abfragen, sondern dies automatisiert mit einer geeigneten VBA-Prozedur erledigen. Die einfachste Variante dieser Prozedur finden Sie in Bild 2 einen Verweis auf die Bibliothek Microsoft XML 3.0 oder höher hinzu.
Listing 1: Einfache Funktion zum Aufrufen des Webservices und zum Ausgeben des Ergebnisses
Public Function AdressePruefen()
Dim objDocument As New MSXML2.DOMDocument
Dim strRequest As String
strRequest = "http://maps.google.com/maps/api/geocode/xmladdress=Borkhofer+Str.,Duisburg&sensor=false"
With objDocument
.async = False
.preserveWhiteSpace = False
.validateOnParse = True
.resolveExternals = False
End With
If objDocument.Load(strRequest) = True Then
Debug.Print objDocument.XML
Else
Debug.Print "#Fehler beim Aufruf"
End If
End Function
Bild 2: Hinzufügen eines Verweises auf die XML-Bibliothek von Microsoft
Die Prozedur AdressePruefen deklariert zunächst das Objekt objDocument, das nach einem Aufruf des Google-Webservices mit dessen Antwort gefüllt werden soll.
Die Variable strRequest nimmt die URL mit der Anfrage auf. Nach dem Einstellen einiger Eigenschaften von objDocument, auf die wir hier nicht im Detail eingehen, wird die Anfrage als Parameter der Load-Funktion von objDocument ausgewertet. Die Load-Funktion liefert, je nachdem, ob der Aufruf erfolgreich war oder nicht, entweder den Wert True oder False zurück. War der Aufruf erfolgreich, können Sie anschließend über die Eigenschaft XML des Objekts objDocument den Inhalt der Antwort im XML-Format betrachten – diese wird in dieser ersten Version im Direktfenster ausgegeben (s. Bild 3).
Bild 3: Das Ergebnis einer Adress-Abfrage im Direktfenster des VBA-Editors
Einsatzmöglichkeiten
Sie wissen nun, wie Sie eine Anfrage an den Google-Webservice zum Prüfen von Adressen formulieren müssen, und erhalten die Antwort im XML-Format. Nun machen wir uns um die weitere Vorgehensweise Gedanken, was vor allem drei Fragen betrifft:
- Wie stellen wir die Anfrage zusammen, und zwar speziell den Ausdruck mit der zu prüfenden Adresse
- Wie ermitteln wir aus dem Ergebnis im XML-Format die gewünschten Informationen
- Und wie verarbeiten wir diese Informationen weiter
Zusammenstellen der HTTP-Anfrage an Google
Der Aufbau der Anfrage an Google ist bis auf den Wert des Parameters address immer gleich. Wir müssen lediglich die zu prüfenden Adressdaten in eine geeignete Form bringen. Das bedeutet, dass die einzelnen Bestandteile (Straße und Hausnummer, PLZ und Ort sowie gegebenenfalls Land und Bundesland) durch Kommata voneinander getrennt aneinandergereiht werden. Leerzeichen innerhalb dieser Einträge ersetzen wir durch Plus-Zeichen.
XML-Antwort auswerten
Nachdem wir die Anfrage etwa wie in der Prozedur aus Listing 1 abgeschickt haben, erhalten wir ein XML-Dokument zurück. Dieses enthält entweder einen Hinweis darauf, dass die Adresse nicht gefunden oder der Aufruf aus irgendeinem anderen Grund gescheitert ist, oder es liefert die auf Basis der übermittelten Informationen ermittelten Adressdaten. In beiden Fällen müssen wir den Inhalt des XML-Dokuments untersuchen, was wir mit den Mitteln der Microsoft XML 3.0-Bibliothek erledigen.
Adressinformationen weiterverarbeiten
Schließlich müssen wir die so ermittelten Informationen weiterverarbeiten – beispielsweise, indem wir diese über die zuvor ermittelten Adressdaten schreiben und eventuell noch nicht ausgefüllte Felder des Adressbestandes mit den gewonnenen Daten füllen.
Adressen prüfen per Klasse
Da diese Funktionen recht umfangreich sind und möglicherweise in mehr als nur einer Anwendung zum Einsatz kommen könnten, lagern wir diese in eine eigene Klasse aus. Die Beschreibung dieser Klasse folgt im Anschluss, zunächst schauen wir uns ihren praktischen Einsatz an.
Um die Klasse einzusetzen, benötigen Sie zuerst eine Objektvariable zum Speichern der Referenz auf die Klasse:
Dim objAddressChecker As clsAddressChecker
Anschließend erstellen Sie ein Objekt auf Basis der Klasse:
Set objAddressChecker = New clsAddressChecker
Dann weisen Sie einigen Eigenschaften der Klasse die Werte der zu untersuchenden Adresse zu:
With objAddressChecker
.Street = "Borkhofer Str."
.Streetnumber = "17"
.PostalCode = "47137"
.City = "Duisburg"
Schließlich rufen Sie die Funktion CheckAddress auf, die entweder den Wert True oder False zurückliefert. Im Falle des Wertes True möchten wir die gefundenen Informationen im Direktfenster betrachten:
If .CheckAddress = True Then
Debug.Print .Street, .Streetnumber, .PostalCode, .City, .Country, .State, .Latitude, .Longitude
Anderenfalls soll das Direktfenster einfach eine Meldung mit einer Erklärung liefern, warum die Adressprüfung nicht funktioniert hat:
Else
Debug.Print .Message
End If
End With
Dies alles können Sie leicht in eine Do While-Schleife packen, die alle Datensätze einer Adressdatenbank durchläuft. Sie müssen dann statt der hier konkret zugewiesenen Zeichenketten die Inhalte der entsprechenden Felder der Adresstabelle übergeben, damit die Adressen geprüft werden können. Sollte die Methode CheckAddress eine passende Adresse gefunden haben, können Sie die vorhandene Adresse gleich durch die geprüfte und gegebenenfalls ergänzte Adresse ersetzen.
Erstellen der Klasse clsAddressChecker
Wechseln Sie zum VBA-Editor und legen Sie eine neue Klasse namens clsAddressChecker an (Menüeintrag Einfügen|Klassenmodul).
Dieser Klasse wollen wir sowohl unsere bestehenden Adressdaten übergeben als auch die geprüften und überarbeiteten Daten entnehmen. Wir benötigen also in jedem Falle einige private Variablen zum Zwischenspeichern dieser Informationen. Diese Variablen sehen wie folgt aus und speichern Informationen wie Straße, Hausnummer et cetera:
Private m_Street As String Private m_Streetnumber As String Private m_PostalCode As String Private m_Country As String Private m_City As String Private m_State As String Private m_Message As String Private m_Latitude As String Private m_Longitude As String
Damit Sie dem Objekt die Daten der zu prüfenden Adressen nach dem Instanzieren übermitteln können, legen Sie eine Reihe von Property Let-Prozeduren an. Jede dieser Prozeduren tritt nach außen als Eigenschaft der Klasse clsAddressChecker in Erscheinung. Im laufenden Betrieb nehmen diese die Werte entgegen, die den Eigenschaften des Objekts zugewiesen werden, und speichern diese in den entsprechenden privaten Variablen:
Public Property Let Street(str As String)
m_Street = str
End Property
Public Property Let Streetnumber(str As String)
m_Streetnumber = str
End Property
Public Property Let PostalCode(str As String)
m_PostalCode = str
End Property
Public Property Let Country(str As String)
m_Country = str
End Property
Public Property Let City(str As String)
m_City = str
End Property
Public Property Let State(str As String)
m_State = str
End Property
Um eines vorwegzunehmen: Die Klasse soll, wenn die Methode CheckAddress erfolgreich war, die gefundenen und gegebenenfalls korrigierten Werte genau in die Variablen schreiben, in denen auch die von der aufrufenden Instanz übergebenen Ausgangswerte landeten. Damit diese Instanz auf diese in den einzelnen Variablen gespeicherten Werte zugreifen kann, brauchen Sie eine Reihe von Property Get-Prozeduren, die das Gegenteil von dem tun, was die Property Let-Prozeduren erledigen: Sie stellen die Werte der privaten Variablen nach außen als Eigenschaften des Objekts auf Basis der Klasse clsAddressChecker zur Verfügung.
Die jeweils einzige Anweisung weist dabei dem Funktionswert dieser Prozeduren den jeweiligen Variableninhalt zu:
Public Property Get Street() As String
Street = m_Street
End Property
Public Property Get Streetnumber() As String
Streetnumber = m_Streetnumber
End Property
...
Zusätzlich zu den bekannten Eigenschaften können Sie mit den folgenden beiden die geografische Position der betroffenen Adresse ermitteln:
Public Property Get Latitude() As String
Latitude = m_Latitude
End Property
Public Property Get Longitude() As String
Longitude = m_Longitude
End Property
Die letzte der verwendeten Property Get-Prozeduren liefert schließlich einen Erklärungstext für ein eventuelles Scheitern des Aufrufs von CheckAddress:
Public Property Get Message() As String
Message = m_Message
End Property
Die Funktion CheckAddress finden Sie in Listing 2. Sie erwartet keine Parameter und liefert einen Boolean-Wert zurück. Wie aber soll eine gefundene Adresse zurückgeliefert werden, wenn nicht als Parameter der Prüffunktion
Ende des frei verfügbaren Teil. Wenn Du mehr lesen möchtest, hole Dir ...
Testzugang
eine Woche kostenlosen Zugriff auf diesen und mehr als 1.000 weitere Artikel
diesen und alle anderen Artikel mit dem Jahresabo