如何通过Excel web查询从谷歌方向API提取距离?

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:

我在Excel中有一长串的起源和目的地列表，使用webquery我可以填入城市和邮政编码来给出一个webquery，比如:

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?

这将返回一个很长的XML文件，但我需要的只是距离。有没有办法只提取距离值?

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)

或者我应该运行一个宏脚本一个一个地提取距离?(因为每次请求服务器时，格式都大致相同)

1 个解决方案

#1

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

简单的答案是XPath——如果您打算使用任何类型的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:

在Excel中的宏编辑器中，进入工具>引用，添加对“Microsoft XML, v6.0”的引用，现在插入>模块，添加以下代码:

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

要扩展它以覆盖多次旅行，只需循环遍历所需的起源和目的地，将每一对作为参数传递给此过程，然后以您需要的任何格式输出结果

#1