VBA code for retrieving distance between two places from google map

original post: [url]http://stackoverflow.com/questions/3985887/how-can-i-extract-the-distance-from-google-directions-api-via-excel-web-query[/url]

[font=Arial,][size=14px]Sub[/size][size=14px] getDistances[/size][size=14px]()[/size]
[size=14px]Dim[/size][size=14px] xhrRequest [/size][size=14px]As[/size][size=14px] XMLHTTP60
[/size][size=14px]Dim[/size][size=14px] domDoc [/size][size=14px]As[/size][size=14px] DOMDocument60
[/size][size=14px]Dim[/size][size=14px] ixnlDistanceNodes [/size][size=14px]As[/size][size=14px] IXMLDOMNodeList
[/size][size=14px]Dim[/size][size=14px] ixnNode [/size][size=14px]As[/size][size=14px] IXMLDOMNode
[/size][size=14px]Dim[/size][size=14px] lOutputRow [/size][size=14px]As[/size]
[size=14px]Long[/size]
[size=14px]' Read the data from the website[/size]
[size=14px]Set[/size][size=14px] xhrRequest [/size][size=14px]=[/size]
[size=14px]New[/size][size=14px] XMLHTTP60
xhrRequest[/size][size=14px].[/size][size=14px]Open [/size][size=14px]"GET"[/size][size=14px],[/size]
[size=14px]"http://maps.googleapis.com/maps/api/directions/xml?origin=Scoresby&destination=Melborne&sensor=false"[/size][size=14px],[/size]
[size=14px]False[/size][size=14px]
xhrRequest[/size][size=14px].[/size][size=14px]send

[/size][size=14px]' Copy the results into a format we can manipulate with XPath[/size]
[size=14px]Set[/size][size=14px] domDoc [/size][size=14px]=[/size]
[size=14px]New[/size][size=14px] DOMDocument60
domDoc[/size][size=14px].[/size][size=14px]loadXML xhrRequest[/size][size=14px].[/size][size=14px]responseText

[/size][size=14px]' The important bit: select every node called "value" which is the child of a node called "distance" which is[/size]
[size=14px]' in turn the child of a node called "step"[/size]
[size=14px]Set[/size][size=14px] ixnlDistanceNodes [/size][size=14px]=[/size][size=14px] domDoc[/size][size=14px].[/size][size=14px]selectNodes[/size][size=14px]([/size][size=14px]"//step/distance/value"[/size][size=14px])[/size]
[size=14px]' Basic stuff to output the distances[/size][size=14px]
lOutputRow [/size][size=14px]=[/size]
[size=14px]1[/size]
[size=14px]With[/size][size=14px] Worksheets[/size][size=14px]([/size][size=14px]"Sheet1"[/size][size=14px])[/size]
[size=14px].[/size][size=14px]UsedRange[/size][size=14px].[/size][size=14px]ClearContents
    [/size][size=14px]For[/size]
[size=14px]Each[/size][size=14px] ixnNode [/size][size=14px]In[/size][size=14px] ixnlDistanceNodes
        [/size][size=14px].[/size][size=14px]Cells[/size][size=14px]([/size][size=14px]lOutputRow[/size][size=14px],[/size]
[size=14px]1[/size][size=14px]).[/size][size=14px]Value [/size][size=14px]=[/size][size=14px] ixnNode[/size][size=14px].[/size][size=14px]Text
        lOutputRow [/size][size=14px]=[/size][size=14px] lOutputRow [/size][size=14px]+[/size]
[size=14px]1[/size]
[size=14px]Next[/size][size=14px] ixnNode
[/size][size=14px]End[/size]
[size=14px]With[/size]
[size=14px]Set[/size][size=14px] ixnNode [/size][size=14px]=[/size]
[size=14px]Nothing[/size]
[size=14px]Set[/size][size=14px] ixnlDistanceNodes [/size][size=14px]=[/size]
[size=14px]Nothing[/size]
[size=14px]Set[/size][size=14px] domDoc [/size][size=14px]=[/size]
[size=14px]Nothing[/size]
[size=14px]Set[/size][size=14px] xhrRequest [/size][size=14px]=[/size]
[size=14px]Nothing[/size]
[size=14px]End[/size]
[size=14px]Sub[/size][/font]