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

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)

From stackoverflow Michael

• 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

  From barrowc