Geocode right from Excel

Esin S sends along this clever script that uses Yahoo’s REST geocoding service to geocode right in Excel (replace appid=XXXX with your own appID):

Function gCode(Street As String, city As String, state As String, zip As String) As String

Dim XMLDOC As MSXML2.DOMDocument
Dim xm, xm2 As IXMLDOMNodeList
Dim init, ConnectURL As String
Dim temp As IXMLDOMNode
Dim doc As MSXML2.IXMLDOMElement
Dim l As String

init = “http://api.local.yahoo.com/MapsService/V1/geocode?appid=XXXX&“
Street = Replace(Street, ” “, “+”)
state = Replace(state, ” “, “+”)
city = Replace(city, ” “, “+”)
init = init & “street=” & Street & “&city=” & city & “&state=” & state & “&zip=” & zip

Set XMLHTTP = CreateObject(“Msxml2.XMLHTTP.4.0”)
Set XMLDOC = CreateObject(“Msxml2.DOMDocument.4.0”)
strUrl = init
XMLHTTP.Open “GET”, strUrl, False
XMLHTTP.send
If XMLHTTP.Status = 200 Then XMLDOC.loadXML (XMLHTTP.responseXML.XML)
Set doc = XMLDOC.documentElement
l = XMLDOC.childNodes(1).childNodes(0).Attributes(0).nodeValue
gCode = l
End Function

for other elements place this
XMLDOC.childNodes(1).childNodes(0).childNodes(X).nodeTypedValue
where X =
0 = Latitude
1= Longitude
2 = address
3 = city
4 = state
5 = zip
6 = country

This is great because you will have access to the “precision” field which is not available to batchgeocode.com. However, using the REST interface you will be subject to the 5,000 per IP address limit. Assuming you don’t need to geocode more than that, your styling!

Thanks Esin!

UPDATE: Esin points out “Phillip, forgot to mention. Whoever uses the script must include the XML library (Tools -> References) in Visual Basic: “Microsoft XML, v X.0″ where X is the latest version number that user has on their PC.”