Geocode right from Excel

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