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 myRequest.Send 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 exitRoute: ' Tidy up memory Close #nextFileNum Set distanceNode = Nothing Set myDomDoc = Nothing Set myRequest = Nothing End Function