Excel UK Mileage Google Direction Api


New Member
Aug 25, 2010
Hi All,

I currently have a staff timesheet to track hours my staff work. I also use it to track mileage with start & end points.

Ultimately I wanted to make use of the Google directions api to automatically display the miles it suggests for a route (Simply as a quick benchmark).

Now, I found what looked like a great example
Site: http://www.knowology.co.uk/resource...ased-mileage-calculator-using-google-maps-api
(Download: www.knowology.co.uk/attachments/article/34/Generic%20Mileage%20tracker.zip )

however, when I change ANY details it simply returns a value# error. Is it incorrect parameters? It is dated end of Jan 2013, so I would have thought it should work fine?
I have tried to contact the people that made it, however I would imagine it is a fairly standard document & I have been waiting a little while.

I am lost as to how to use the api within my own timesheet without something like the above example to us. FYI I'm not amazing with Excel, however I get by with 'frankensteining' what I need... :P

Last edited:

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
Looks to me like there was some incorrect coding going on behind the scenes. I too got value errors until I tried modifying some the code then I actually recieved values. Not sure if they are correct but they don't look bad on my test versions.
Use Alt + F11 to go into the VBA Editor and replace the entirety of Module1s code with this:

Option Explicit

Public Sub WaitBrowserQuiet(objIE As InternetExplorer)
    Do While objIE.Busy Or objIE.ReadyState <> READYSTATE_COMPLETE
End Sub

Public Function getXPathElement(sXPath As String, objElement As Object) As HTMLBaseElement
    Dim sXPathArray() As String
    Dim sNodeName As String
    Dim sNodeNameIndex As String
    Dim sRestOfXPath As String
    Dim lNodeIndex As Long
    Dim lCount As Long
    ' Split the xpath statement
    sXPathArray = Split(sXPath, "/")
    sNodeNameIndex = sXPathArray(1)
    If Not InStr(sNodeNameIndex, "[") > 0 Then
        sNodeName = sNodeNameIndex
        lNodeIndex = 1
        sXPathArray = Split(sNodeNameIndex, "[")
        sNodeName = sXPathArray(0)
        lNodeIndex = CLng(Left(sXPathArray(1), Len(sXPathArray(1)) - 1))
    End If
    sRestOfXPath = Right(sXPath, Len(sXPath) - (Len(sNodeNameIndex) + 1))
    Set getXPathElement = Nothing
    For lCount = 0 To objElement.ChildNodes().Length - 1
        If UCase(objElement.ChildNodes().Item(lCount).nodeName) = UCase(sNodeName) Then
            If lNodeIndex = 1 Then
                If sRestOfXPath = "" Then
                    Set getXPathElement = objElement.ChildNodes().Item(lCount)
                    Set getXPathElement = getXPathElement(sRestOfXPath, objElement.ChildNodes().Item(lCount))
                End If
            End If
            lNodeIndex = lNodeIndex - 1
        End If
    Next lCount
End Function

Public Function GetDistance(From As String, Destination As String, ReturnJourney As Boolean) As String
    Dim ie As InternetExplorer
    Dim elem As HTMLBaseElement
    Dim url As String
    url = Replace(Replace(Range("confURL"), "{0}", From), "{1}", Destination)
    Set ie = New InternetExplorer
    ie.Visible = False
    ie.Navigate url
    WaitBrowserQuiet ie
    Set elem = getXPathElement(Range("confXPath").Value, ie.Document)
    Dim distance As String
    If elem.ChildNodes().Length = 0 Then
        GetDistance = "ERR"
        distance = elem.ChildNodes().Item(1).NodeValue
        If Not IsNumeric(distance) Then
            GetDistance = -1
            Dim journeyLength As Long
            journeyLength = CLng(distance) / Range("confMultiplier") ' Conversion from yards to miles
            GetDistance = journeyLength * (1 - CInt(ReturnJourney)) ' Doubles the distance for a return journey
        End If
    End If
    Set ie = Nothing
End Function

Don't ask me how it works, I just noticed some small inconsistencies!
Upvote 0
Perfect - thanks for this :)

I made a couple of tweaks, just as it didnt seem to exactly line up with Google Maps (I think it was a conversion error?)

I changed the multiplier to "1609.790576", I also changed "Dim journeyLength As Long" to "Dim journeyLength As Single" - simply so it didnt round up the numbers automatically.

Now - I think it might be my IE that is the problem, but it works a bit hit & miss - any suggestions?

I forgot to add - does it recheck all the entries each time excel is started? Or only when a change is made? - I only ask as this could potentially slow everything down if there was quite a bit of data?
Last edited:
Upvote 0
Ahhh!! That code looks much better. Now forgive my limited knowledge but your code doesn't call IE does it? So it would be quicker? I notice that the original code I used hogs resources by not closing down the IE process.

This may sound cheeky (because it is) BUT, is there a way to use your more efficient sexier code, with the google maps api? I only ask as it has taken us literally months to get the staff in the habit of checking Google maps for journeys and if we now switch to Bing I will have a mini staff revolution, simply due to the discrepancies between the two services.

Last edited:
Upvote 0
Yes, and it's actually significantly easier. However it breaches their terms and conditions - their api can only be used to display data on a google map; Bing doesn't have this stipulation which is why I've used it for the example.

You are correct, my code doesn't use IE, automating IE is extraordinarily slow - the code I linked to is almost instantaneous; certainly fast enough to use as a worksheet function anyway :)
Upvote 0
OH. I also realised the google code opens 2 instances of IE and only closes one, which is potentially why the resources are getting hogged? One window returns an invalid response and stays open.... Any ideas?

PS I may just bite the bullet then and face the revolution..!!
Last edited:
Upvote 0
IF you're so inclined, my code is very simple, just step through it and try to figure out how it works - as a hint if you really want to use google despite their T&Cs (which you're already breaking using ie in the way you are) then my code would be almost identical ;)
Upvote 0

Forum statistics

Latest member

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back