Batch Geocode Addresses Using Bing Maps API and an Excel Worksheet

One of the projects that I’m working on nowadays, required me to batch geocode almost 8,000 addresses into latitude and longitude coordinates, and then display it on a Google map, using Google’s fusion table. All my data lives in an Excel worksheet. I’m not familiar with this world of geocoding, Google Maps API, etc. but I had to find a solution in order to make this process easy.

I thought about the idea of having a macro as part of my excel worksheet, that will connect to Google Maps / Bing maps and will pull data from my worksheet, geocode it using the API and will return the latitude and longitude coordinates.

While researching the web, I found out that someone already thought about it, and created a wonderful tool, based on an Excel worksheet and Bing Maps’ API. It’s called Excel Geocoding Tool.

How to batch geocode addresses with Bing Maps and Microsoft Excel

So the process is pretty simple:

1. Download the Excel file from the Excel Geocoding Tool website.
2. If the file is opened in “Protected View” (yellow bar on the top of the page), click “Enable Editing”.
3. Enable Macros:
4. Enable macros in Excel 2013 (Office 365): File -> Options -> on the left side, choose “Trust Center” -> click on “Trust Center Settings”.
5. Enable macros in Excel 2007,2010: Click on the Office button -> “Excel Options” -> “Trust Center” -> “Trust Center Settings” -> “Macro Settings”.
6. Under “Macro Settings” choose “Enable all macros” and check the “Trust access to the VBA project object model”.
7. When you’re done using the Excel Geocoding Tool, don’t forget to disable the macros.
8. In order to use the tool, you need to create a Bing Maps API key.
9. Once you get your key, copy and paste it in the “Bing Maps Key” field:

Now all you have to do, is navigate to the next sheet called “Geocode”, paste your addresses in the “location” column and click “Geocode all rows”, or “Geocode selected rows”:

Don’t forget that if you get the Bing Trial API key, you’re limited to 10,000 addresses in total. If you need more, just create another account (hotmail.com, outlook.com) and get another key.