WiGLE is a popular platform which can be used for finding the location of a device using the names of WiFi networks in its vicinity. I’ve written about this before, and wrote some Python code to interact with their API. This API has since been retired and replaced with a new one, as of December […]
I had a comment from Gabriel on the Google Distance post, asking if it could be adapted. After a bit of back-and-forth by email with Google Translate as an intermediary (Gabriel is Brazilian and my live-in translator/interpreter/girlfriend is in Brazil too so she couldn’t help), I figured out that what Gabriel wanted was to return the time taken rather than the distance.
This sounded like it might be a useful addition – you’d be able to work out things like average speed travelled. And it also turned out not to be too difficult, needing just a few changes to the function.
The first one is to change
"//leg/duration/value". That was easy enough to find out by referring to the XML schema for the Google Maps Directions API.
The other main thing to handle is that the Google API returns the duration in seconds, which needs converting into a more useful format. I decided to give two options with this – to return it as decimal hours where 1 1/2 hours is represented as 1.5 hours, and to return it in Excel’s date format where 1 1/2 hours is shown as 01:30:00. I chose the second as default.
The syntax for using the function is as follows:
=G_TIME(Origin, Destination) to return the time taken in Excel time format (you’ll need to format the cell to make it display correctly).
=G_TIME(Origin, Destination, "Decimal") to return the time taken as a decimal.
Function G_TIME(Origin As String, Destination As String, _
Optional Format As String = "Date") As Double
' 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 timeNode As IXMLDOMNode
G_TIME = 0
On Error GoTo exitRoute
' Check and clean inputs
Origin = Replace(Origin, " ", "%20")
Destination = Replace(Destination, " ", "%20")
' 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
' Make the XML readable usign XPath
Set myDomDoc = New DOMDocument60
' Get the time node value
Set timeNode = myDomDoc.SelectSingleNode("//leg/duration/value")
If Format = "Decimal" Then ' Return as a decimal - 30 mins as 0.5 hrs
G_TIME = timeNode.Text / 3600 ' Seconds in an hour
Else 'Return in Excel's 00:00:00 date format - 30 mins as 00:30:00
G_TIME = timeNode.Text / 86400 ' Seconds in a day
' Tidy up
Set timeNode = Nothing
Set myDomDoc = Nothing
Set myRequest = Nothing