Geocoding with Microsoft Excel and Bing Maps API

Geocoding with Microsoft Excel and Bing Maps API

I wrote a tutorial about how to do geocoding with Microsoft Excel and Google Maps but saw few comments about the limitation which is 2500 addresses. Surfing the internet I found interesting number from Bing maps which allows you to perform 10000 addresses in total. If you need more than that then create a new MSN or Hotmail account and get the new Bing map API key.

So somebody has  already created a excel template with all the required coding, you just have to create a Bing map API key.

 

1. Download the excel sheet or go to the http://excelgeocodingtool.com/ and download it.

2. Open the downloaded excel file and if it is open in protected view (highlight yellow bar on the top) then click enable editing.

3. After that process, enable macros.

4. In Excel 2013: Go to File -> Options -> on the left side, choose Trust Center -> click on Trust Center Settings and Macro Settings

5. In Excel 2007, 2010: Click on the Office button -> Excel Options -> Trust Center -> Trust Center Settings-> Macro Settings.

6. Then check the Enable all macros

Note: Make sure to disable macros once geocoding process is completed for the security purpose.

 

Below image is from Microsoft Excel 2013


7. To process the geocoding you have to generate Bing API key, go to Bing Maps API key. This will take you to login page, you can use your Hotmail or MSN account to login in. Once the initial process is completed you are ready to generate the key. To generate the key click on My account and then on My keys.


8. Then fill the value according to the nature of your project or anything that you like to fill up. Then click on Create button, which will take a while to display the key, see below image.



9. Go back to your open Geocoding Excel template and paste the generated code into the text box of Bings Map API.


10. Then go to Geocode sheet, add all your addresses under the Location column and press the Geocode Selected rows or Geocode all rows button. This will generate Latitude, Longitude and other info.


As mentioned above, you can create new MSN account to generate new API key when your 10000 limit is over. Hope you liked the tutorial and also, if you have any question please comment.

This Post Has 23 Comments

  1. not working i get a runtime error 9 subscript out of range at
    ‘set lat/long/confidence
    latitude = geocodeData(0)

  2. Hi, thank you so much. very nice!

  3. I have the latitude = geocodeData(0) error even though i’m using ur address. Why is this happening

  4. Can you please send directly to my e-mail? IT blocked the website for download.

    1. Sorry what I have to send you, did not got you very well

  5. Thank you so much for sharing . It works

    1. Thanks

  6. not working i get a runtime error 9 subscript out of range

  7. changed to excel 2016 and worked, thank you

  8. Dear admin,

    I love this tool but can you do reverse geocoding?
    I have a data of Lat & Long and would like to reverse it to the address location , so as to get the Province, Ward, City etc.

  9. The worksheet works really well, very easy to use and accurate. I should point out to others if you aren’t aware, this spreadsheet calls on the REST API which allows up to 125,000 billable calls per year at no charge, after that I’m not sure how much Microsoft charges per additional call. Details are in the link provided.

    https://www.microsoft.com/en-us/maps/create-a-bing-maps-key

    1. Thank you

  10. Hi
    This tool is amazing !!!! For me is works on
    Win7 SP1 MS Office 365 Pro Excel 2016 ( Vers 1705)
    Thanks heaps !!!
    Cheeers, Jens

    1. Thank you

  11. hello, i would like to use the excel geocoder but it doesn’t seem to work. i have enabled the macros (set it to medium security level). could it be a problem that i don’t have excel but only open office on my computer? sorry about this silly question! 🙂

  12. Worked like a charm!!! Thanksss 🙂
    I had exactly 10,000 addresses to be geocoded! I am so glad i found your article!!

    1. Thank you:)

  13. Thanks for the coding & file. However failed to run by under
    Win 7 + Excel 2013.

    Anyone could guide on the Proxy? And will it possible on the “Reference – VBAProject” should tick anything to make it works?

  14. I need to encode addresses in Brazil, is it possible?

  15. not working i get a runtime error 9 subscript out of range

  16. Hi – I’m getting the Run-time error ‘9’ – is there a fix for this? Many thanks!

  17. Fabulous work…Love it…NOW to add the KM’s between 2 addresses. Will post this here is you like

  18. thanks, its working.
    would you add some option to reverse geocode (lat, long) in to an address ?

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Close Menu