chelston
New Member
Hi All! I'm pretty new to VBA so this is probably embarrassingly simple, but how do I use google maps/api to obtain lat/long of zip codes, then plug that into a haversine formula for approximate "as the crow flies" distances?
OR
Is there any way to change the mode to "flight" or some version of a straight line calculation in line 6 of the following? (I have zip codes and need approximate flight distances.)
'Calculate Google Maps distance between two addresses
>>> use code - tags <<<
OR
Is there any way to change the mode to "flight" or some version of a straight line calculation in line 6 of the following? (I have zip codes and need approximate flight distances.)
'Calculate Google Maps distance between two addresses
>>> use code - tags <<<
Code:
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=pl&sensor=false&key=AIzaSyD8EtmS82qghp2RcmCIFgjxtsot-5xeWu8"
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
Last edited by a moderator: