我怎样才能通过提取的Excel Web查询从谷歌地图API的距离？ [英] How can I extract the distance from Google Directions API via Excel web query?

问题描述

我在Excel中的起点和终点的一个长长的清单，使用webquery我可以在城市和邮政code填给像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:

<一个href=\"http://maps.googleapis.com/maps/api/directions/xml?origin=Scoresby&destination=Melborne&sensor=false\" rel=\"nofollow\">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中的宏编辑器，选择工具>引用，并添加引用微软XML，V6.0现在插入>模块并添加此code：

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查询从谷歌地图API的距离？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！