PDA

View Full Version : Intermittent issue with a UDF, Google address extractor.



georgiboy
09-11-2018, 07:31 AM
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