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 = “”
    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
    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