{"id":600,"date":"2016-11-09T14:15:04","date_gmt":"2016-11-09T19:15:04","guid":{"rendered":"http:\/\/blogs.shu.edu\/digitalhumanities\/?p=600"},"modified":"2019-04-16T11:29:47","modified_gmt":"2019-04-16T15:29:47","slug":"get-geolocations-with-excel","status":"publish","type":"post","link":"https:\/\/blogs.shu.edu\/digitalhumanities\/2016\/11\/09\/get-geolocations-with-excel\/","title":{"rendered":"Get Geolocations With Excel"},"content":{"rendered":"

I found this to be incredibly useful when I needed to get coordinates for\u00a0a set of hundreds of addresses. The original is from\u00a0policeanalyst.com,\u00a0<\/strong>with instructions updated for Excel 2013 from\u00a0http:\/\/grindgis.com\/<\/a>.<\/p>\n

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().<\/p>\n

Set Up The Developer Tab<\/h3>\n

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:<\/p>\n

\"exceloptions\"<\/a><\/p>\n

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…<\/p>\n

Add Your VB Code<\/h3>\n

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:<\/p>\n

\"vb\"<\/a><\/p>\n

After you click the Visual Basic icon, select the Insert dropdown, and pick Module.<\/p>\n

\"module\"<\/a><\/p>\n

Paste this code into the panel:<\/p>\n

Function MyGeocode(address As String) As String\r\n Dim strAddress As String\r\n Dim strQuery As String\r\n Dim strLatitude As String\r\n Dim strLongitude As String\r\n strAddress = URLEncode(address)\r\n 'Assemble the query string\r\n strQuery = \"http:\/\/maps.googleapis.com\/maps\/api\/geocode\/xml?\"\r\n strQuery = strQuery & \"address=\" & strAddress\r\n strQuery = strQuery & \"&sensor=false\"\r\n 'define XML and HTTP components\r\n Dim googleResult As Object\r\n Set googleResult = CreateObject(\"MSXML2.DOMDocument.6.0\")\r\n Dim googleService As Object\r\n Set googleService = CreateObject(\"MSXML2.XMLHTTP.6.0\")\r\n Dim oNodes As MSXML2.IXMLDOMNodeList\r\n Dim oNode As MSXML2.IXMLDOMNode\r\n 'create HTTP request to query URL - make sure to have\r\n 'that last \"False\" there for synchronous operation\r\n googleService.Open \"GET\", strQuery, False\r\n googleService.send\r\n googleResult.LoadXML (googleService.responseText)\r\n Set oNodes = googleResult.getElementsByTagName(\"geometry\")\r\n If oNodes.Length = 1 Then\r\n For Each oNode In oNodes\r\n strLatitude = oNode.ChildNodes(0).ChildNodes(0).Text\r\n strLongitude = oNode.ChildNodes(0).ChildNodes(1).Text\r\n MyGeocode = strLatitude & \",\" & strLongitude\r\n Next oNode\r\n Else\r\n MyGeocode = \"Not Found (try again, you may have done too many too fast)\"\r\n End If\r\n End Function\r\n Public Function URLEncode(StringVal As String, Optional SpaceAsPlus As Boolean = False) As String\r\n Dim StringLen As Long: StringLen = Len(StringVal)\r\n If StringLen > 0 Then\r\n ReDim result(StringLen) As String\r\n Dim i As Long, CharCode As Integer\r\n Dim Char As String, Space As String\r\n If SpaceAsPlus Then Space = \"+\" Else Space = \"%20\"\r\n For i = 1 To StringLen\r\n Char = Mid$(StringVal, i, 1)\r\n CharCode = Asc(Char)\r\n Select Case CharCode\r\n Case 97 To 122, 65 To 90, 48 To 57, 45, 46, 95, 126\r\n result(i) = Char\r\n Case 32\r\n result(i) = Space\r\n Case 0 To 15\r\n result(i) = \"%0\" & Hex(CharCode)\r\n Case Else\r\n result(i) = \"%\" & Hex(CharCode)\r\n End Select\r\n Next i\r\n URLEncode = Join(result, \"\")\r\n End If\r\n End Function\r\n\r\n\r\n<\/pre>\n
Add a Reference To It<\/h3>\n
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.<\/p>\n
\"xml\"<\/a><\/p>\n
Check the box, click OK.<\/p>\n
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.”<\/p>\n
Start Using It<\/h3>\n
Then, when you’re ready to use it, go to File::Options::Add-Ins. Select the new Geocode Application Add-in and click OK.<\/p>\n
\"add-ins\"<\/a><\/p>\n
For example, in a new spreadsheet enter an address in cell A1, such as<\/p>\n
400 South Orange Ave., South Orange, NJ 07079<\/pre>\n
Then in another cell, add this code:<\/p>\n
=MyGeocode(A1)<\/pre>\n
And as soon as you hit enter or tab, you should see this:<\/p>\n
40.7425259,-74.2460794<\/pre>\n
You can then use the usual Excel tools to drag this across as many lines as you need.<\/p>\n
For more details, see the grindgis.com<\/a> page referenced above. Police Analyst doesn’t seem to have the original post up anymore, but they do have a lot of intriguing\u00a0Excel\/VB code examples<\/a> to look through (VLOOKUP, anyone?)<\/p>\n","protected":false},"excerpt":{"rendered":"
If you don’t mind tinkering under the hood with your copy of Excel, here’s a method you can use to add some code and create a new function that generates the latitude and longitude of an address. <\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_et_pb_use_builder":"","_et_pb_old_content":"","_et_gb_content_width":"","_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"footnotes":""},"categories":[9],"tags":[96,97,98,99,90,77,95],"class_list":["post-600","post","type-post","status-publish","format-standard","hentry","category-tools","tag-add-ons","tag-geocode","tag-geolocation","tag-macros","tag-mapping","tag-programming","tag-vb"],"_links":{"self":[{"href":"https:\/\/blogs.shu.edu\/digitalhumanities\/wp-json\/wp\/v2\/posts\/600","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/blogs.shu.edu\/digitalhumanities\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/blogs.shu.edu\/digitalhumanities\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/blogs.shu.edu\/digitalhumanities\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/blogs.shu.edu\/digitalhumanities\/wp-json\/wp\/v2\/comments?post=600"}],"version-history":[{"count":4,"href":"https:\/\/blogs.shu.edu\/digitalhumanities\/wp-json\/wp\/v2\/posts\/600\/revisions"}],"predecessor-version":[{"id":1225,"href":"https:\/\/blogs.shu.edu\/digitalhumanities\/wp-json\/wp\/v2\/posts\/600\/revisions\/1225"}],"wp:attachment":[{"href":"https:\/\/blogs.shu.edu\/digitalhumanities\/wp-json\/wp\/v2\/media?parent=600"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blogs.shu.edu\/digitalhumanities\/wp-json\/wp\/v2\/categories?post=600"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blogs.shu.edu\/digitalhumanities\/wp-json\/wp\/v2\/tags?post=600"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}