I found this to be incredibly useful when I needed to get coordinates for a set of hundreds of addresses. The original is from policeanalyst.com, with instructions updated for Excel 2013 from http://grindgis.com/.
The process involves adding a new item to your ribbon for developer tools, adding a block of Visual Basic code, referencing it to Microsoft XML, and saving it. When you use it in a workbook, you’ll summon up your new add-in and then use the new function, =MyGeocode().
Set Up The Developer Tab
First open a new blank workbook in Excel.Then go to the File menu, and at the bottom of the left column you’ll see “Options.” Click that and you’ll see this screen:
You’ll select “Customize Ribbon,” then check off the box next to “Developer.” Then click the OK button. You’ve got a new tab on your ribbon now. Click on it for the next step, which is…
Add Your VB Code
Try not to be daunted by this; unless you’re accustomed to code language it looks like gobbledygook. All you need to do is some copying and pasting. First (and this is a difference between Office 2013 and previous versions) click on the Visual Basic tab in the Developer ribbon:
After you click the Visual Basic icon, select the Insert dropdown, and pick Module.
Paste this code into the panel:
Function MyGeocode(address As String) As String Dim strAddress As String Dim strQuery As String Dim strLatitude As String Dim strLongitude As String strAddress = URLEncode(address) 'Assemble the query string strQuery = "http://maps.googleapis.com/maps/api/geocode/xml?" strQuery = strQuery & "address=" & strAddress strQuery = strQuery & "&sensor=false" 'define XML and HTTP components Dim googleResult As Object Set googleResult = CreateObject("MSXML2.DOMDocument.6.0") Dim googleService As Object Set googleService = CreateObject("MSXML2.XMLHTTP.6.0") Dim oNodes As MSXML2.IXMLDOMNodeList Dim oNode As MSXML2.IXMLDOMNode 'create HTTP request to query URL - make sure to have 'that last "False" there for synchronous operation googleService.Open "GET", strQuery, False googleService.send googleResult.LoadXML (googleService.responseText) Set oNodes = googleResult.getElementsByTagName("geometry") If oNodes.Length = 1 Then For Each oNode In oNodes strLatitude = oNode.ChildNodes(0).ChildNodes(0).Text strLongitude = oNode.ChildNodes(0).ChildNodes(1).Text MyGeocode = strLatitude & "," & strLongitude Next oNode Else MyGeocode = "Not Found (try again, you may have done too many too fast)" End If End Function Public Function URLEncode(StringVal As String, Optional SpaceAsPlus As Boolean = False) As String Dim StringLen As Long: StringLen = Len(StringVal) If StringLen > 0 Then ReDim result(StringLen) As String Dim i As Long, CharCode As Integer Dim Char As String, Space As String If SpaceAsPlus Then Space = "+" Else Space = "%20" For i = 1 To StringLen Char = Mid$(StringVal, i, 1) CharCode = Asc(Char) Select Case CharCode Case 97 To 122, 65 To 90, 48 To 57, 45, 46, 95, 126 result(i) = Char Case 32 result(i) = Space Case 0 To 15 result(i) = "%0" & Hex(CharCode) Case Else result(i) = "%" & Hex(CharCode) End Select Next i URLEncode = Join(result, "") End If End Function
Add a Reference To It
In the same window, look under the Tools menu and pick References. Here you just want to make sure that Microsoft XML v6.0 is checked off. You might have to scroll down to find it.
Check the box, click OK.
Then save it (ctrl-S), as the type “Excel Add-In”. Give it a useful name — one you’ll recognize later. I called mine “Geocode.”
Start Using It
Then, when you’re ready to use it, go to File::Options::Add-Ins. Select the new Geocode Application Add-in and click OK.
For example, in a new spreadsheet enter an address in cell A1, such as
400 South Orange Ave., South Orange, NJ 07079
Then in another cell, add this code:
And as soon as you hit enter or tab, you should see this:
You can then use the usual Excel tools to drag this across as many lines as you need.
For more details, see the grindgis.com page referenced above. Police Analyst doesn’t seem to have the original post up anymore, but they do have a lot of intriguing Excel/VB code examples to look through (VLOOKUP, anyone?)