Google Maps direkt in MS Excel anzapfen

01. Feb 2019 | Blog

VON Florian Nachbagauer

Trotz des Zeitalters von BigData ist es nicht absehbar, dass MS Excel von unseren Büro- und Privatcomputern verschwindet. Es bietet doch unzählige Möglichkeiten, kleinere Datenmengen aufzubewahren, anschaulich aufzubereiten und diverse Berechnungen durchzuführen. Deshalb ist es auch interessant, direkt von MS Excel aus verschiedene Datenquellen anzuzapfen. Für automatisierte Abfragen stellt Google (und auch viele andere Anbieter von Daten) eine sogenannte Web-API, also Programmierschnittstelle, zur Verfügung, welche mittels VBA auch in Excel abgefragt werden kann.

 

Angenommen: Sie müssen eine Kilometerabrechnung machen. Sie sind das letzte Jahr dienstlich sehr viel herumgefahren, haben aber nicht jedes Mal den Stand Ihres Kilometerzählers notiert. Sie haben eine lange Liste von Orten, an denen Sie waren und bräuchten nun die Auto-Distanz dazwischen. Sie könnten jetzt jede einzelne Strecke bei Google Maps händisch abfragen und dann in eine Excel-Datei kopieren. Allerdings haben wir angenommen, dass Sie im letzten Jahr derart viele Strecken hatten, dass diese Arbeit viel zu mühsam ist. Viel einfacher wäre es doch, wenn sie einfach eine Excel-Formel aufrufen, die Ihre Strecken automatisch abfragt und einfügt. Glücklicherweise ist das möglich. Sie müssen nur eine VBA-Funktion definieren, die genau das macht. Und was wäre das für ein Blogbeitrag, wenn Sie den zugehörigen jetzt selber schreiben müssten, denn der folgende Code macht genau das:

 

'Calculate Google Maps distance between two addresses
Public Function GetDistance(start As String, dest As String)
    Dim firstVal As String, secondVal As String, lastVal As String
    firstVal = "
https://maps.googleapis.com/maps/api/distancematrix/json?origins="
    secondVal = "&destinations="
    lastVal = "&mode=car&language=en&sensor=false&key=YOUR_KEY"
    Set objHTTP = CreateObject("MSXML2.ServerXMLHTTP")
    Url = firstVal & Replace(start, " ", "+") & secondVal & Replace(dest, " ", "+") & lastVal
    objHTTP.Open "GET", Url, False
    objHTTP.setRequestHeader "User-Agent", "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0)"
    objHTTP.send ("")
    If InStr(objHTTP.responseText, """distance"" : {") = 0 Then GoTo ErrorHandl
    Set regex = CreateObject("VBScript.RegExp"): regex.Pattern = """value"".*?([0-9]+)": regex.Global = False
    Set matches = regex.Execute(objHTTP.responseText)
    tmpVal = Replace(matches(0).SubMatches(0), ".", Application.International(xlListSeparator))
    GetDistance = CDbl(tmpVal)
    Exit Function
ErrorHandl:
    GetDistance = -1
End Function


Damit dieser Code funktioniert, müssen Sie YOUR_KEY durch einen gültigen Key für die Google API ersetzen. Um einen Key zu bekommen, muss man bei der Google Maps Plattform einen Account eröffnen. Überschreitet man mit der Anzahl der Abfragen ein gewisses monatliches Kontingent nicht, ist es gratis. Das genaue Kontingent ändert sich manchmal, kann aber aktuell unter dem Link nachgelesen werden.

 

Ein anderer Anwendungsfall wäre zum Beispiel, wenn man zu einer Reihe von Adressen die Geokoordinaten braucht. Auch hierfür gibt es eine API. Und falls Sie bereits dabei sind, die Excel-Welt zu verlassen, um sich DataScience Tools wie R zuzuwenden: In R sind derartige API-Anfragen in der Regel noch einfacher, weil es die zugehörige Funktion meistens schon gibt und Sie sie nicht selbst zusammenschustern müssen.

 

Eine genauere Anleitung, wie der obige VBA-Code zum Laufen gebraucht werden kann, findet sich hier.

Hier können Sie den Verfasser gerne kontaktieren: florian.nachbagauer@grawe.at