I am currently using this VBA-Code, written by Jamie Bull, to calculate both, driving distance, as well as driving duration, between approx. 1.500 villages and a specific shopping center, using excel.

Google shows me the driving time for each trip, considering the current traffic situation. But I only need a kind of average driving time or the driving time without traffic situation, in order to be able to compare the driving times for each rout all else being equal.

Using the google Maps website, I can see several driving times: Current time and time without traffic. Is there a way to integrate this into the code?

By the way: Are there any information about, which driving speed is used for calculating the driving time (without traffic)?

Option Explicit

Function G_DISTANCE(Origin As String, Destination As String, Optional Requery As Boolean) As Double
' Developed by Jamie Bull at oCoCarbon - www.ococarbon.com
' Requires a reference to Microsoft XML, v6.0
' Draws on the stackoverflow answer at bit.ly/parseXML
Dim myRequest As XMLHTTP60
Dim myDomDoc As DOMDocument60
Dim distanceNode As IXMLDOMNode
Dim tempFile As String
Dim strTemp As String
Dim nextFileNum As Long
    G_DISTANCE = 0
    On Error GoTo exitRoute
    Origin = CleanQuery(Origin)
    Destination = CleanQuery(Destination)
    ' Check for cached version
    tempFile = Environ("temp") & "\" & Origin & "_" & Destination & ".tmpdst"
    If (Len(Dir(tempFile)) = 0) Or Requery Then ' query Google
        ' Read the XML data from the Google Maps API
        Set myRequest = New XMLHTTP60
        myRequest.Open "GET", "http://maps.googleapis.com/maps/api/directions/xml?origin=" _
            & Origin & "&destination=" & Destination & "&sensor=false", False
    Else ' otherwise query the temp file
        nextFileNum = FreeFile
        Open tempFile For Input As #nextFileNum
        Line Input #nextFileNum, strTemp
        G_DISTANCE = strTemp / 1000
        GoTo exitRoute
    End If
    ' Make the XML readable using XPath
    Set myDomDoc = New DOMDocument60
    myDomDoc.LoadXML myRequest.responseText
    ' Get the distance node value
    Set distanceNode = myDomDoc.SelectSingleNode("//leg/distance/value")
    If Not distanceNode Is Nothing Then
        G_DISTANCE = distanceNode.Text / 1000
        'Cache the result if not cached
        If (Len(Dir(tempFile)) = 0) Then Call CreateFile(tempFile, distanceNode.Text)
    End If
    ' Tidy up memory
    Close #nextFileNum
    Set distanceNode = Nothing
    Set myDomDoc = Nothing
    Set myRequest = Nothing
End Function

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.