Use google Maps API to find distances between locations
Problem Statement
I've come across a situation where a business client of mine needed to find the distance between a source location to multiple destinations.
There were hundreds of destinations, and so this is no easy task to do manually.
As I did this some time back, I've forgotten the original sources from which I learnt the method. I apologize to them for not citing the references.
Solution
I snooped around online for a way to find the distances, and after getting a fair idea of how google maps works, I was able to put together this macro.
First, create a properly formatted Excel sheet like so:
The first three columns are details of the origin, while the next three columns are details of the destinations.
You can add or remove the number of columns depending on the precision you require.
The seventh column is for displaying the calculated distances.
Open up the VBA editor through 'Alt-F11' and add a new module.
Paste the following code:
Sub getDistances()
Dim xhrRequest As XMLHTTP60
Dim domDoc As DOMDocument60
Dim ixnlDistanceNodes As IXMLDOMNodeList
Dim ixnNode As IXMLDOMNode
Dim lOutputRow As Long
Dim dist As Double
dist = 0
Dim LastRow As Integer
Dim i As Integer
LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
For i = 2 To LastRow Step 1
' Read the data from the website
Set xhrRequest = New XMLHTTP60
xhrRequest.Open "GET", "http://maps.googleapis.com/maps/api/directions/xml?origin=" & Range("A" & i).Value & "+" & Range("B" & i).Value & "+" & Range("C" & i).Value & "&destination=" & Range("D" & i).Value & "+" & Range("E" & i).Value & "+" & Range("F" & i).Value & "&sensor=false", False
xhrRequest.send
' Copy the results into a format we can manipulate with XPath
Set domDoc = New DOMDocument60
domDoc.LoadXML xhrRequest.responseText
' The important bit: select every node called "value" which is the child of a node called "distance" which is
' in turn the child of a node called "step"
Set ixnlDistanceNodes = domDoc.SelectNodes("//step/distance/value")
' Basic stuff to output the distances
lOutputRow = 1
For Each ixnNode In ixnlDistanceNodes
dist = dist + Val(ixnNode.Text)
Next ixnNode
Cells(i, 7).Value = dist / 1000
Set ixnNode = Nothing
Set ixnlDistanceNodes = Nothing
Set domDoc = Nothing
Set xhrRequest = Nothing
dist = 0
'Give enough time for the data to come back
Application.Wait Now + TimeSerial(0, 0, 1)
Next i
End Sub
Aaaaaand... we're done!
Run the "getDistances" macro, sit back and relax while the distances are being populated.