VBA retrieving Google Geocoding XML ChildNodes
<p>I currently got some address data records some are from the Netherlands which are correctly registered and and some are foreign which are not registered correctly.</p>

<p>So in order to get these addresses registered correctly I am trying to make a macro that adds the missing records to the data records by using the Google Geocoding API.</p>

<p>While trying to retrieve the results from the XML record I get a run-time error. 
The error is: Run-time error '-2147467259 (80004005)':
expected token 'eof' found '='.</p>

<p>//result/address_component/long_name/type-->=&lt;--street_name</p>

<p>As you can see I am having difficulties with retreiving the differnt childNodes I need.</p>

<p>A link to the workbook with the working code, you can find the cod in the second module. (<a href="https://drive.google.com/open?id=0B1TucCM4DOwydnp5RDg0elJwRDQ" rel="nofollow noreferrer">https://drive.google.com/open?id=0B1TucCM4DOwydnp5RDg0elJwRDQ</a>)</p>

<p>An example of the xml output from the google API or see below: </p>

<p><a href="https://developers.google.com/maps/documentation/geocoding/intro" rel="nofollow noreferrer">https://developers.google.com/maps/documentation/geocoding/intro</a></p>

<p>As of now I got the following code: </p>

<pre><code>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?" _
        &amp; "&amp;address=" &amp; Cells(GeoCount, 1) &amp; " " &amp; Cells(GeoCount, 2) &amp; " " &amp; Cells(GeoCount, 3) &amp; "&amp;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
</code></pre>

<p>example off the XML return: </p>

<pre><code>&lt;GeocodeResponse&gt;
 &lt;status&gt;OK&lt;/status&gt;
 &lt;result&gt;
  &lt;type&gt;street_address&lt;/type&gt;
  &lt;formatted_address&gt;1600 Amphitheatre Pkwy, Mountain View, CA 94043, USA&lt;/formatted_address&gt;
  &lt;address_component&gt;
   &lt;long_name&gt;1600&lt;/long_name&gt;
   &lt;short_name&gt;1600&lt;/short_name&gt;
   &lt;type&gt;street_number&lt;/type&gt;
  &lt;/address_component&gt;
  &lt;address_component&gt;
   &lt;long_name&gt;Amphitheatre Pkwy&lt;/long_name&gt;
   &lt;short_name&gt;Amphitheatre Pkwy&lt;/short_name&gt;
   &lt;type&gt;route&lt;/type&gt;
  &lt;/address_component&gt;
  &lt;address_component&gt;
   &lt;long_name&gt;Mountain View&lt;/long_name&gt;
   &lt;short_name&gt;Mountain View&lt;/short_name&gt;
   &lt;type&gt;locality&lt;/type&gt;
   &lt;type&gt;political&lt;/type&gt;
  &lt;/address_component&gt;
  &lt;address_component&gt;
   &lt;long_name&gt;San Jose&lt;/long_name&gt;
   &lt;short_name&gt;San Jose&lt;/short_name&gt;
   &lt;type&gt;administrative_area_level_3&lt;/type&gt;
   &lt;type&gt;political&lt;/type&gt;
  &lt;/address_component&gt;
  &lt;address_component&gt;
   &lt;long_name&gt;Santa Clara&lt;/long_name&gt;
   &lt;short_name&gt;Santa Clara&lt;/short_name&gt;
   &lt;type&gt;administrative_area_level_2&lt;/type&gt;
   &lt;type&gt;political&lt;/type&gt;
  &lt;/address_component&gt;
  &lt;address_component&gt;
   &lt;long_name&gt;California&lt;/long_name&gt;
   &lt;short_name&gt;CA&lt;/short_name&gt;
   &lt;type&gt;administrative_area_level_1&lt;/type&gt;
   &lt;type&gt;political&lt;/type&gt;
  &lt;/address_component&gt;
  &lt;address_component&gt;
   &lt;long_name&gt;United States&lt;/long_name&gt;
   &lt;short_name&gt;US&lt;/short_name&gt;
   &lt;type&gt;country&lt;/type&gt;
   &lt;type&gt;political&lt;/type&gt;
  &lt;/address_component&gt;
  &lt;address_component&gt;
   &lt;long_name&gt;94043&lt;/long_name&gt;
   &lt;short_name&gt;94043&lt;/short_name&gt;
   &lt;type&gt;postal_code&lt;/type&gt;
  &lt;/address_component&gt;
  &lt;geometry&gt;
   &lt;location&gt;
    &lt;lat&gt;37.4217550&lt;/lat&gt;
    &lt;lng&gt;-122.0846330&lt;/lng&gt;
   &lt;/location&gt;
   &lt;location_type&gt;ROOFTOP&lt;/location_type&gt;
   &lt;viewport&gt;
    &lt;southwest&gt;
     &lt;lat&gt;37.4188514&lt;/lat&gt;
     &lt;lng&gt;-122.0874526&lt;/lng&gt;
    &lt;/southwest&gt;
    &lt;northeast&gt;
     &lt;lat&gt;37.4251466&lt;/lat&gt;
     &lt;lng&gt;-122.0811574&lt;/lng&gt;
    &lt;/northeast&gt;
   &lt;/viewport&gt;
  &lt;/geometry&gt;
  &lt;place_id&gt;ChIJ2eUgeAK6j4ARbn5u_wAGqWA&lt;/place_id&gt;
 &lt;/result&gt;
&lt;/GeocodeResponse&gt;
</code></pre>