Access / Excel VBA proc to calculate distance using Google Maps

You can slot in this sub to calculate the distance between two points.

Don’t call it for industrial numbers of points or you’ll get throttled… if you can, put it through as one request with waypoints.

Public Sub GoogleGetDistance(ByVal startFrom As String, ByVal endAt As String, ByRef refDistance As Double, ByRef refTime As Date)
    Dim url As String
    refDistance = -1
    refTime = 0
    url = “
http://maps.googleapis.com/maps/api/directions/xml?”
    url = url & “origin=” & startFrom & “&”
    url = url & “destination=” & endAt & “&”
   
    url = url & “optimize:false&”
    url = url & “sensor=false&region=uk”
   
    ‘MsgBox url
   
    If Len(Dir(“temproute.xml”, vbNormal)) > 0 Then Kill “temproute.xml”
    ‘AppendToFile “temproute.xml”, url
   
    ‘Shell “notepad temproute.xml”, vbNormalFocus
   
    Dim req As MSXML2.XMLHTTP
    Set req = New MSXML2.XMLHTTP
    req.Open “GET”, url, False
    req.send
   
    Dim resp As String
    resp = req.responseText
   
    Dim xdoc As MSXML2.DOMDocument
    Set xdoc = req.responseXML
   
    xdoc.Save “temprouteresult.xml”
    ‘Shell “notepad temprouteresult.xml”, vbNormalFocus
   
    Dim l As IXMLDOMNode
    Set l = xdoc.selectSingleNode(“DirectionsResponse/route/leg”)
    If Not IsNull(l) Then
        refTime = CLng(l.selectSingleNode(“duration/value”).Text) / 60 / 60 / 24
        refDistance = CLng(Format(CLng(l.selectSingleNode(“distance/value”).Text) / 1609, “0.0”))
    End If
   

End Sub