{"id":2135,"date":"2016-01-24T19:29:35","date_gmt":"2016-01-24T19:29:35","guid":{"rendered":"https:\/\/grindgis.com\/?p=2135"},"modified":"2018-04-05T14:30:00","modified_gmt":"2018-04-05T14:30:00","slug":"geocoding-excel-and-google","status":"publish","type":"post","link":"https:\/\/grindgis.com\/?p=2135","title":{"rendered":"Geocoding using Microsoft Excel 2013 and Google Maps API"},"content":{"rendered":"

Geocoding is the process of converting addresses to the x and y coordinate or longitude and latitude. There are various option available in the market to perform geocoding, some are paid and some are free. You can use The\u00a0Google Maps Geocoding API<\/a> to retrieve the longitude and latitude for free. Later you can convert this longitude and latitude to your desire coordinate values, there are number of online tools for doing that. So it will be hassle to perform each addresses one at time in google, for that you can use Excel software to perform large number of addresses. You create excel function to perform the geocoding for multiple addresses.<\/p>\n

For this task I am using Microsoft Excel 2013, you can use 2007 or 2010 version. First task is to turn on the Developer tab. To find this tab in 2007 or 2010 version you have to play around.<\/p>\n

1.<\/strong><\/span> Go to the Microsoft File > Options > Customize Ribbon <\/strong>and check Developer
\n<\/strong><\/p>\n

\"\"
\n<\/strong><\/p>\n

You will notice Developer tab is added on the ribbon.
\n<\/strong><\/p>\n

\"\"
\n<\/strong><\/p>\n

2. <\/strong><\/span>Now click the Visual Basic button on the far left corner which will open the new window. Now we want a new module to insert which will store our new function. For that click on Insert > New Module<\/strong><\/p>\n

\"\"
\n<\/strong><\/p>\n

Copy the below code and paste into the empty area (code source: policeanalyst.com)
\n<\/strong><\/p>\n

\n\n\n<\/colgroup>\n\n\n
\n
Function MyGeocode(address As String) As String\r\n<\/em><\/span><\/code><\/pre>\n
  Dim strAddress As String\r\n<\/em><\/span><\/code><\/pre>\n
  Dim strQuery As String\r\n<\/em><\/span><\/code><\/pre>\n
  Dim strLatitude As String\r\n<\/em><\/span><\/code><\/pre>\n
  Dim strLongitude As String\r\n<\/em><\/span><\/code><\/pre>\n
  strAddress = URLEncode(address)\r\n<\/em><\/span><\/code><\/pre>\n
  'Assemble the query string\r\n<\/em><\/span><\/code><\/pre>\n
  strQuery = \"http:\/\/maps.googleapis.com\/maps\/api\/geocode\/xml?\"\r\n<\/em><\/span><\/code><\/pre>\n
  strQuery = strQuery & \"address=\" & strAddress \r\n<\/em><\/span><\/code><\/pre>\n
  strQuery = strQuery & \"&sensor=false\"\r\n<\/em><\/span><\/code><\/pre>\n
  'define XML and HTTP components\r\n<\/em><\/span><\/code><\/pre>\n
  Dim googleResult As New MSXML2.DOMDocument\r\n<\/em><\/span><\/code><\/pre>\n
  Dim googleService As New MSXML2.XMLHTTP\r\n<\/em><\/span><\/code><\/pre>\n
  Dim oNodes As MSXML2.IXMLDOMNodeList\r\n<\/em><\/span><\/code><\/pre>\n
  Dim oNode As MSXML2.IXMLDOMNode\r\n<\/em><\/span><\/code><\/pre>\n
  'create HTTP request to query URL - make sure to have\r\n<\/em><\/span><\/code><\/pre>\n
  'that last \"False\" there for synchronous operation\r\n<\/em><\/span><\/code><\/pre>\n
  googleService.Open \"GET\", strQuery, False\r\n<\/em><\/span><\/code><\/pre>\n
  googleService.send\r\n<\/em><\/span><\/code><\/pre>\n
  googleResult.LoadXML (googleService.responseText)\r\n<\/em><\/span><\/code><\/pre>\n
  Set oNodes = googleResult.getElementsByTagName(\"geometry\")\r\n<\/em><\/span><\/code><\/pre>\n
  If oNodes.Length = 1 Then\r\n<\/em><\/span><\/code><\/pre>\n
    For Each oNode In oNodes\r\n<\/em><\/span><\/code><\/pre>\n
      strLatitude = oNode.ChildNodes(0).ChildNodes(0).Text\r\n<\/em><\/span><\/code><\/pre>\n
      strLongitude = oNode.ChildNodes(0).ChildNodes(1).Text\r\n<\/em><\/span><\/code><\/pre>\n
      MyGeocode = strLatitude & \",\" & strLongitude\r\n<\/em><\/span><\/code><\/pre>\n
    Next oNode\r\n<\/em><\/span><\/code><\/pre>\n
  Else\r\n<\/em><\/span><\/code><\/pre>\n
    MyGeocode = \"Not Found (try again, you may have done too many too fast)\"\r\n<\/em><\/span><\/code><\/pre>\n
  End If\r\n<\/em><\/span><\/code><\/pre>\n
End Function\r\n<\/em><\/span><\/code><\/pre>\n
Public Function URLEncode(StringVal As String, Optional SpaceAsPlus As Boolean = False) As String\r\n<\/em><\/span><\/code><\/pre>\n
  Dim StringLen As Long: StringLen = Len(StringVal)\r\n<\/em><\/span><\/code><\/pre>\n
  If StringLen>0 Then\r\n<\/em><\/span><\/code><\/pre>\n
    ReDim result(StringLen) As String\r\n<\/em><\/span><\/code><\/pre>\n
    Dim i As Long, CharCode As Integer\r\n<\/em><\/span><\/code><\/pre>\n
    Dim Char As String, Space As String\r\n<\/em><\/span><\/code><\/pre>\n
    If SpaceAsPlus Then Space = \"+\" Else Space = \"%20\"\r\n<\/em><\/span><\/code><\/pre>\n
    For i = 1 To StringLen\r\n<\/em><\/span><\/code><\/pre>\n
      Char = Mid$(StringVal, i, 1)  \r\n<\/em><\/span><\/code><\/pre>\n
      CharCode = Asc(Char)\r\n<\/em><\/span><\/code><\/pre>\n
      Select Case CharCode\r\n<\/em><\/span><\/code><\/pre>\n
      Case 97 To 122, 65 To 90, 48 To 57, 45, 46, 95, 126\r\n<\/em><\/span><\/code><\/pre>\n
        result(i) = Char\r\n<\/em><\/span><\/code><\/pre>\n
      Case 32\r\n<\/em><\/span><\/code><\/pre>\n
        result(i) = Space\r\n<\/em><\/span><\/code><\/pre>\n
      Case 0 To 15\r\n<\/em><\/span><\/code><\/pre>\n
        result(i) = \"%0\" & Hex(CharCode)\r\n<\/em><\/span><\/code><\/pre>\n
      Case Else\r\n<\/em><\/span><\/code><\/pre>\n
        result(i) = \"%\" & Hex(CharCode)\r\n<\/em><\/span><\/code><\/pre>\n
      End Select\r\n<\/em><\/span><\/code><\/pre>\n
    Next i\r\n<\/em><\/span><\/code><\/pre>\n
    URLEncode = Join(result, \"\")\r\n<\/em><\/span><\/code><\/pre>\n
  End If\r\n<\/em><\/span><\/code><\/pre>\n
End Function<\/em><\/span><\/code><\/pre>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n
This code make use of Microsoft’s XML library, so you have to add references to it. To add the reference go to Tools > References
\n<\/strong><\/p>\n
You will see list of references with the check box, select the Microsoft XML v6.0, <\/strong>after that click Ok button.<\/p>\n
\"\"<\/p>\n
Finally save your Visual basic window by pressing save button. Then from the drop down menu of Save as type select Excel Add-In, <\/strong>assign the file name of your desire. Once the file in saved in “*.xlam” extension you can open any new document and use the function. Close all the open excel files and open the new one.<\/p>\n
3. <\/strong><\/span>Open the new excel file and then go to file > Options > Add-Inns. From the Manage drop down box select the Excel-Add Ins.
\n<\/strong><\/p>\n
\"\"<\/p>\n
After selecting then press the GO<\/strong> button, this will list all the saved Add-ins files, as I have saved as Book1. Xlam I will check that box. Then press Ok after selecting,<\/p>\n
\"\"<\/p>\n
4. <\/strong><\/span>As everything is done to perform the geocode, we are going to use the function to retrieve the longitude and latitude value. Let us suppose I have address 65 Hawthorn way, Olds, Alberta, Canada<\/strong> then you type that value in excel and use the function MyGeocode<\/strong> (as you type my<\/strong> it will show function on the box).
\n<\/span><\/p>\n
\"\"
\n<\/span><\/p>\n
It will take few seconds to run the function because it has to pull the information from the google server and then result will display.
\n<\/span><\/p>\n
\"\"
\n<\/span><\/p>\n
Now if you have number of addresses and you want to run the geocode then this function will help you. Just keep in mind few things while using this function, after the execution process is done then you can copy and paste the values because each time you open the file then it will processing each cells which might take up few minutes to perform it.
\n<\/span><\/p>\n
Second things as it is free they will not allow you to do perform thousands of geocode at once, so best way is to have a 10 seconds break between addresses. If Google stop you for retrieving large number of addresses, you will see error saying “Not Found (try again, you may have done too many too fast)”.<\/strong> See below example, I have tried few number of addressee without following 10 seconds rule.
\n<\/span><\/p>\n
\"\"
\n<\/span><\/p>\n
I hope you liked this tutorial, please comment if you have any question regarding this.
\n<\/span><\/p>\n","protected":false},"excerpt":{"rendered":"
Geocoding is the process of converting addresses to the x and y coordinate or longitude and latitude. There are various option available in the market to perform geocoding, some are paid and some are free. You can use The\u00a0Google Maps Geocoding API to retrieve the longitude and latitude for free. Later you can convert this […]<\/p>\n","protected":false},"author":1,"featured_media":2140,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[43],"tags":[46,383,382,381,380],"class_list":["post-2135","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-microsoft-excel","tag-46","tag-excel-and-geocode","tag-geocoding","tag-google-api","tag-microsoft-excel","entry","has-media"],"aioseo_notices":[],"_links":{"self":[{"href":"https:\/\/grindgis.com\/index.php?rest_route=\/wp\/v2\/posts\/2135","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/grindgis.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/grindgis.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/grindgis.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/grindgis.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=2135"}],"version-history":[{"count":0,"href":"https:\/\/grindgis.com\/index.php?rest_route=\/wp\/v2\/posts\/2135\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/grindgis.com\/index.php?rest_route=\/wp\/v2\/media\/2140"}],"wp:attachment":[{"href":"https:\/\/grindgis.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=2135"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/grindgis.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=2135"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/grindgis.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=2135"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}