Automatische Fahrtdauerberechnung
26.05.2017 14:10:35
Gustavo
Hallo Gustavo,
ich nutze dazu diese Lösung:
http://analystcave.com/ _
excel-calculate-distances-between-addresses/# _
Calculate_distance_between_two_addresses_using_Google_Maps_in_Excel
'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 = "http://maps.googleapis.com/maps/api/distancematrix/json?origins="
secondVal = "&destinations="
lastVal = "&mode=car&language=pl&sensor=false"
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
und in deiner Exceltabelle schreibst du dann:
D3 =GetDistance(B3;C3)
und bekommst die Strecke in (m).
Gruß,
Rainer
Kann man diese Lösung eventuell auf die Fahrtdauer übertragen, bei google maps wird ja auch die Fahrtdauer angezeigt.