如何通过 Excel 网络查询从 Google Directions API 中提取距离? [英] How can I extract the distance from Google Directions API via Excel web query?

问题描述

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

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

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

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)

推荐答案

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

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

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

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

这篇关于如何通过 Excel 网络查询从 Google Directions API 中提取距离?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！