检索Geocoding的VBA
检索Geocoding的VBA
提问于 2016-10-25 23:59:45
我目前得到了一些地址数据记录，一些来自荷兰，它们正确注册，还有一些是外国的，有些没有正确注册。
因此，为了正确注册这些地址，我尝试使用创建一个宏，将丢失的记录添加到数据记录中。
当试图从XML记录中检索结果时，我会得到一个运行时错误。错误是:运行时错误'-2147467259 (80004005)'：预期的令牌'eof‘found '=’。
//result/address_component/long_name/type-->=<--street_name
正如你所看到的，我很难收回我所需要的不同的childNodes。
链接到带有工作代码的工作簿，您可以在第二个模块中找到cod。(https://drive.google.com/open?id=0B1TucCM4DOwydnp5RDg0elJwRDQ)
一个来自google的xml输出示例，请参见下面的示例：
https://developers.google.com/maps/documentation/geocoding/intro
到目前为止，我得到了以下代码：
Option Explicit
Sub geocode()
Dim Request As New XMLHTTP30
Dim Results As New DOMDocument30
Dim StatusNode As IXMLDOMNode
Dim GeoCount As Integer
Dim AutoResult As Integer
For GeoCount = 2 To 10
On Error GoTo errorhandler
Request.Open "GET", "http://maps.googleapis.com/maps/api/geocode/xml?" _
& "&address=" & Cells(GeoCount, 1) & " " & Cells(GeoCount, 2) & " " & Cells(GeoCount, 3) & "&sensor=false", False
Request.send
Results.LoadXML Request.responseText
Set StatusNode = Results.SelectSingleNode("//status")
If UCase(StatusNode.Text) = "OK" Then
Cells(GeoCount, 4) = Results.SelectSingleNode("//result/address_component/long_name/type=street_number").Text
Cells(GeoCount, 5) = Results.SelectSingleNode("//result/address_component/long_name/type=route").Text
Cells(GeoCount, 6) = Results.SelectSingleNode("//result/address_component/long_name/type=postal_code").Text
Cells(GeoCount, 7) = Results.SelectSingleNode("//result/address_component/long_name/type=locality").Text
Cells(GeoCount, 8) = Results.SelectSingleNode("//result/address_component/long_name/type=country").Text
Cells(GeoCount, 9) = Results.SelectSingleNode("//result/geometry/location/lat").Text
Cells(GeoCount, 10) = Results.SelectSingleNode("//result/geometry/location/lng").Text
Else
For AutoResult = 4 To 10
Cells(GeoCount, AutoResult) = UCase(StatusNode.Text)
Next AutoResult
End If
Set StatusNode = Nothing
Set Results = Nothing
Set Request = Nothing
errorhandler:
Set StatusNode = Nothing
Set Results = Nothing
Set Request = Nothing
Next GeoCount
End SubXML返回的示例：
<GeocodeResponse>
<status>OK</status>
<result>
<type>street_address</type>
<formatted_address>1600 Amphitheatre Pkwy, Mountain View, CA 94043, USA</formatted_address>
<address_component>
<long_name>1600</long_name>
<short_name>1600</short_name>
<type>street_number</type>
</address_component>
<address_component>
<long_name>Amphitheatre Pkwy</long_name>
<short_name>Amphitheatre Pkwy</short_name>
<type>route</type>
</address_component>
<address_component>
<long_name>Mountain View</long_name>
<short_name>Mountain View</short_name>
<type>locality</type>
<type>political</type>
</address_component>
<address_component>
<long_name>San Jose</long_name>
<short_name>San Jose</short_name>
<type>administrative_area_level_3</type>
<type>political</type>
</address_component>
<address_component>
<long_name>Santa Clara</long_name>
<short_name>Santa Clara</short_name>
<type>administrative_area_level_2</type>
<type>political</type>
</address_component>
<address_component>
<long_name>California</long_name>
<short_name>CA</short_name>
<type>administrative_area_level_1</type>
<type>political</type>
</address_component>
<address_component>
<long_name>United States</long_name>
<short_name>US</short_name>
<type>country</type>
<type>political</type>
</address_component>
<address_component>
<long_name>94043</long_name>
<short_name>94043</short_name>
<type>postal_code</type>
</address_component>
<geometry>
<location>
<lat>37.4217550</lat>
<lng>-122.0846330</lng>
</location>
<location_type>ROOFTOP</location_type>
<viewport>
<southwest>
<lat>37.4188514</lat>
<lng>-122.0874526</lng>
</southwest>
<northeast>
<lat>37.4251466</lat>
<lng>-122.0811574</lng>
</northeast>
</viewport>
</geometry>
<place_id>ChIJ2eUgeAK6j4ARbn5u_wAGqWA</place_id>
</result>
</GeocodeResponse>回答 1
Stack Overflow用户
回答已采纳
发布于 2016-10-26 00:57:46
您需要根据其同级long_name元素的值来选择type。
例：
Cells(GeoCount, 4) = Results.SelectSingleNode( _
"//result/address_component[type='street_number']/long_name").Text页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/40251618
复制相关文章
相似问题
腾讯云开发者
