如何通过Excel Web查询提取Google Directions API的距离？

问题描述

我在Excel中有很长的起始和目的地列表，使用webquery我可以填写城市和邮政编码来给出如下的网页查询：

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

或者我应该运行一个宏脚本来逐一提取距离？ （因为每次我问服务器时格式保持不变）

解决方案

简短的答案是 - 如果您要使用任何类型的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 
 
'从网站读取数据
设置xhrRequest =新建XMLHTTP60 
 xhrRequest.OpenGET，http： /maps.googleapis.com/maps/api/directions/xml?origin=Scoresby&destination=Melborne&sensor=false，False 
 xhrRequest.send 
 
'将结果复制到格式我们可以使用XPath 
设置domDoc = N ew DOMDocument60 
 domDoc.loadXML xhrRequest.responseText 
 
'重要的位：选择每个称为value的节点，它是一个名为distance的节点的子节点，它是
'反过来称为step的节点的子节点
设置ixnlDistanceNodes = domDoc.selectNodes（// step / distance / value）
 
'输出距离的基本东西
 lOutputRow = 1 
与工作表（Sheet1）
 .UsedRange.ClearContents 
对于每个ixnNode在ixnlDistanceNodes 
 .Cells（lOutputRow，1）.Value = ixnNode.Text 
 lOutputRow = lOutputRow + 1 
下一个ixnNode 
结束
 
设置ixnNode = Nothing 
设置ixnlDistanceNodes =没有
设置domDoc =没有
 Set xhrRequest = Nothing 
 
 End Sub 
  

将其扩展到您可以通过多次路线循环遍历所需的起点和目的地，将每一对作为参数传递给此过程，然后输出结果，以垫你需要

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

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)

解决方案

The short answer is XPath - well worth learning if you are going to work with any kind of 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:

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 Web查询提取Google Directions API的距离？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！