Hi all,

I have an issue with a UDF that returns information from Google about a given postal code.

The issue is that it will intermittently return a blank at times, even if it is looking at a column populated with the exact same postal code.

My goal in the end is to pass it a full postal code and for it to return to me the name of the road and the town, either this or every detail about the postal code even country.

Hopefully i have explained the issue well enough, please see code below.

Thanks in advance

Function getzip(myAddress As String) As String

    Dim objHttp As Object
    Dim tempString As String
    Dim xmlResults As MSXML2.DOMDocument
    Dim xmlEle As MSXML2.IXMLDOMNode


    myAddress = Replace(myAddress, " ", "+")
    myUrl = "http://maps.google.com/maps/api/geocode/xml?address=" & myAddress & "&output=xml&sensor=false"
    
    Set xmlResults = New MSXML2.DOMDocument
    
    Set objHttp = CreateObject("MSXML2.ServerXMLHTTP")
    Call objHttp.Open("GET", myUrl, False)
    Call objHttp.send("")
    
    xmlResults.LoadXML (objHttp.responseText)
    
    For Each xmlEle In xmlResults.ChildNodes(1).ChildNodes(1).SelectNodes("address_component")
        If xmlEle.ChildNodes(2).nodeTypedValue = "postal_town" Then
            getzip = xmlEle.ChildNodes(0).nodeTypedValue
        End If
    Next xmlEle
    
    Set xmlResults = Nothing
    Set objHttp = Nothing
End Function