excel - 如何通过 Excel 网络查询从 Google Directions API 中提取距离？

Question

我在 Excel 中有一个很长的起点和目的地列表，使用 webquery 我可以填写城市和邮政编码以提供如下 webquery:

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

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

还是我应该只运行一个宏脚本来一一提取距离？ (因为每次我询问服务器时格式都大致相同)

Answer 1

简短的回答是XPath - 如果您要使用任何类型的 XML，非常值得学习

在 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

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