VBA检索Google地理编码XML子节点

我目前得到了一些地址数据记录,有些来自荷兰,注册正确,有些来自国外,注册不正确。

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

错误为:运行时错误“-2147467259(80004005)”: 应为标记“eof”,但找到“=”。

//result/address_component/long_name/type-->=<--street_name

如您所见,我在检索所需的不同子节点时遇到了困难。

带有工作代码的工作簿链接,您可以在第二个模块中找到cod。( https://drive.google.com/open?id=0B1TucCM4DOwydnp5RDg0elJwRDQ )

google API的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>