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.”
