Excel UK Mileage Google Direction Api

[ongobay]

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

Thanks,
Chris

 

[AD_Taylor]

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
        DoEvents
    Loop
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
    Else
        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)
                Else
                    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"
        
    Else
        distance = elem.ChildNodes().Item(1).NodeValue
        
        If Not IsNumeric(distance) Then
            GetDistance = -1
            
        Else
            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
    
    ie.Quit
    Set ie = Nothing
End Function

Don't ask me how it works, I just noticed some small inconsistencies!

 

[ongobay]

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?

 

[Kyle123]

Have a look at this tip I wrote, it's much faster and much more simple Getting Distances and DriveTimes

 

[ongobay]

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.

Thanks,
Chris

 

[Kyle123]

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

 

[ongobay]

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..!!

 

[Kyle123]

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