How can I extract the distance from Google Directions API via Excel web query?

Question

I have a long list of origins and destinations in Excel, using webquery I can fill in the cities and postal code to give a webquery like:

http://maps.googleapis.com/maps/api/directions/xml?origin=Scoresby&destination=Melborne&sensor=false

This returns me a long XML file, but all I need is just the distance. Is there a way to extract only the distance value?

Or should I just run a macro script to extract distance one by one? (Since the format remains roughly the same each time I ask the server)

Solution

The short answer is XPath - well worth learning if you are going to work with any kind of XML

In the macro editor in Excel, go to Tools > References and add a reference to "Microsoft XML, v6.0" Now Insert > Module and add this code:

Sub getDistances()

Dim xhrRequest As XMLHTTP60
Dim domDoc As DOMDocument60
Dim ixnlDistanceNodes As IXMLDOMNodeList
Dim ixnNode As IXMLDOMNode
Dim lOutputRow As Long

' Read the data from the website
Set xhrRequest = New XMLHTTP60
xhrRequest.Open "GET", "http://maps.googleapis.com/maps/api/directions/xml?origin=Scoresby&destination=Melborne&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
With Worksheets("Sheet1")
    .UsedRange.ClearContents
    For Each ixnNode In ixnlDistanceNodes
        .Cells(lOutputRow, 1).Value = ixnNode.Text
        lOutputRow = lOutputRow + 1
    Next ixnNode
End With

Set ixnNode = Nothing
Set ixnlDistanceNodes = Nothing
Set domDoc = Nothing
Set xhrRequest = Nothing

End Sub

To extend this to cover multiple trips you would just loop through the required origins and destinations, pass each pair as parameters to this procedure and then output the results in whichever format you need