excel marco google map retreive xml data

modifed for work


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=address&destination=address&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("//leg/distance/text")

Set ixnNode = domDoc.SelectSingleNode("//leg/distance/text")

' Basic stuff to output the distances
Range("B2").Value = ixnNode.Text



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

End Sub