Filling in the gaps with  Mapping APIs  get it now

This article will cover 2 main topics
  • How to use the most common APIs for GeoCoding (clean up an address and find it's co-ordinates)
  • Comparing various GeoCoding APIS
If you are interested in how all this is coded, or how the APIS compare, you can read this and subsequent pages.  Read the summary blog entry comparing Bing, Yahoo, Ovi and Google Maps.

Try it out first ?

If you just want a to try it, then you can download an example application (googleMapping.xlsm) and come back here later.
For a quick start, try Step by Step Guides

Note that you will may need to modify the filenames for the output files to some directory that suits you before running. These filenames can be found in the GeoCoding Worksheet and look like this - just change the 'Code' Column for filename in each of the parameter blocks you find to something appropriate for your PC. Some directories on your machine may be readonly to you - for example if you have downloaded this and run it from the download directory on windows 8 you may not be able to create a file in that directory because of local permission settings. You should set this to a directory you have permission to create files in.

Quick start

  1. Download the  Google Maps workbook
  2. Amend Columns A,B,C in the VenueMaster workbook to your data
  3. Hit one of the buttons on that page to geocode (with Google, Bing, yahoo , create a KML file or plot the addresses on a map using Google, Yahoo, Bing or OVI.
  4. Read how it works below.  You will normally not need to do any coding - just possible tweak a parameter sheet. For example here is all that is needed to Plot addresses on a google map or a Bing Map, where the argument is the name of a parameter sheet.
Public Sub bingMarkingVizVenues()
    bingMarkingExample cVizAppVenues
End Sub
Public Sub googleMarkingVizVenues()
    googleMarkingExample cVizAppVenues
End Sub


I have also implemented an example using Bing Maps for comparison with the Google Maps API, as well as Yahoo Placemaker example

Directly from Excel, using geoCoding APIs we will

Compare them yourself

In the downloadable example, googleMapping.xlsm these are implemented side by side and you can test their relative capability to deal with the same incomplete data. 


Now we have our data geocoded, we can start looking at marking up that data on Maps.

You want to learn Google Apps Script?

Learning Apps Script, (and transitioning from VBA) are covered comprehensively in my my book, Going Gas - from VBA to Apps script, available All formats are available now from O'Reilly,Amazon and all good bookshops. You can also read a preview on O'Reilly

If you prefer Video style learning I also have two courses available. also published by O'Reilly.
Google Apps Script for Developers and Google Apps Script for Beginners.