检索Geocoding的VBA

Question

我目前得到了一些地址数据记录，一些来自荷兰，它们正确注册，还有一些是外国的，有些没有正确注册。

因此，为了正确注册这些地址，我尝试使用创建一个宏，将丢失的记录添加到数据记录中。

当试图从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 Sub

XML返回的示例：

<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>

Answer 1

您需要根据其同级long_name元素的值来选择type。

例：

Cells(GeoCount, 4) = Results.SelectSingleNode( _
     "//result/address_component[type='street_number']/long_name").Text