Deprecated
Google has now stopped its Earth API and Maps API is a paid for API nowadays. Some capabilities have also been either removed or changed. Therefore, I had to remove all examples of VizMap applications I had created. I didn’t remove the entire topic as I thought some of the code may still be useful with some modifications. 

Plotting markers on on a Google Earth

This article will cover how to create a basic KML file so that your Excel data can be mapped on Google Earth. There is a more advanced implementation in Data Driven Mapping applications which can plot complex google visualizations on top of either Google Maps or Google Earth right out of Excel.

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.  Note that you may want 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 ther Marker KML parameter block 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.
Note also that by default, a session of Google Earth (or whatever you have associated with the kml suffix), will kick off after generation of the file. You can change this behavior in the control parameter block by setting the browser to ‘none’ if you only want to create the file and not execute it.

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.

What is KML

KML is a markup language for describing how to display geographic data to Google Earth and other great  resources. Just as we can map your data to google maps straight of Excel, we can do the same with Google Earth or other KML capable systems by generating a simple KML file. The process is no different and the VBA code just as simple as with the mapping solutions.
Here’s what you get –
and here is what a generated KML file looks like.

VBA walkthrough

We normally use jSon to communicate with mapping packages, but since kml is xml based we need different approach to formatting the output data. Aside from this there is no difference in the code to that point.
Since this application is generic and data driven, we simply need to pass the name of the parameter sheet for the particular dataset to the generic marking module, and let it know that we are in this case going to need KML output rather than an HTML file complete with generated javaScript.
The only difference in the generic Marking module is to call a different outputfile generator
Which is the trivial conversion exercise below

This is of course only scratching the surface of the capabilities of Google Earth. Check back here for more interesting KML/ Excel integration or take a look at Data Driven Mapping applications for how to create mapping applications straight out of Excel with no javaScript to write.

For help and more information join our forum, follow the blog, follow me on Twitter