vba 如何通过 Excel 网络查询从 Google Directions API 中提取距离？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3985887/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How can I extract the distance from Google Directions API via Excel web query?
提问by Michael
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
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)
或者我应该运行一个宏脚本来一个一个地提取距离？（因为每次询问服务器时格式都大致相同）
回答by barrowc
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
要将其扩展到涵盖多次旅行，您只需遍历所需的起点和目的地，将每一对作为参数传递给此过程，然后以您需要的任何格式输出结果
