Home > Computers and Internet > Access / Excel VBA proc to calculate distance using Google Maps

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

  1. July 19, 2011 at 3:00 am

    Thanks! This was some code I needed and I was able to get it to work for my client. I changed it to a function and added code to return the refDistance value. Works like a charm!

    • SKAY
      October 13, 2011 at 11:49 am

      I tried this code and doesn’t work. Am I doing something wrong? It’s complaining object is not set. Any help?

      • October 13, 2011 at 6:23 pm

        In your code window, do tools-references and check you have a reference to “Microsoft XML, v6.0”

  2. Skay
    October 17, 2011 at 12:39 pm

    I did, but still I’m getting the error. Is it any other reference needs to be set?

  3. Skay
    October 17, 2011 at 12:48 pm

    OK, I found the issue. Actually the xml file (temprouteresult.xml) doesn’t have the singlenode we’re looking for.

    Here is the xml file:


    Any idea???

  4. poseido
    January 30, 2012 at 6:33 pm

    Skay, i did face same issue, but was just because the URL was worg,

    try to uncomment MsgBox url and see if the url generated is right (copy it to the browser and see if it’s well constructed and provides data, example:

    RIGHT: http://maps.googleapis.com/maps/api/directions/xml?origin=27400&destination=28036&optimize:false&sensor=false&region=uk

    WRONG (ntoe the space after “origin”): http://maps.googleapis.com/maps/api/directions/xml?origin =27400&destination=28036&optimize:false&sensor=false&region=uk

  5. poseido
    January 30, 2012 at 6:35 pm

    by the way, thanks a lot for the code! (even tough ir breaks the agreement with google :D)

  6. poseido
    January 30, 2012 at 7:06 pm

    what I do not figure out is how to use the data retrieved, I can use within the function, but to show it in a form, or add it to a table… no idea

  7. poseido
    February 2, 2012 at 1:02 pm

    another thing… the code returns data in miles or km depending on the country I’m on…
    it’s possible to force it to provide km or miles, but always same?

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


Get every new post delivered to your Inbox.

Join 79 other followers

%d bloggers like this: