Google Maps In Excel

July 28th, 2005 | Categories: XML/Services | Tags: , , , , ,

Here’s my first attempt at using Google maps in excel (download below). Currently I can input an address and have a Google map of the address displayed in Excel, with most of the cool google functionality.

A couple thumbnails of the Excel Google Map Spreadsheet, click to view the larger images.

Normal View
smallwhitehouse
Zoomed Hybrid View
smallwhitehouse2

The Google Map Spreadsheet uses two API’s to work, the Geocoder.us Api and Google Maps Api. I thought about also throwing in the current weather report, but refrained for now.

So how does it work?

Summary:
1. The address is sent to Geocoder.us to be converted to Latitude and Longitude (required to map a location on google maps), and the result is returned to the spreadsheet.

2. Excel sends this geocoded information to the automateexcel.com server, where I have a google map that receives latitude and longitude as variables and displays the respective map via the Google Map API.

3. Finally there’s a web browser control in Excel that navigates to this new address.

A bit more:
1. To experiment with sending an address and having Geocoder.us return the Latitude and Longitude back to Excel, I created a Geocoding workbook to experiment with.

2. Click this link to see my webpage that receives latitude and longitude as variables and returns the respective map (look in the address bar). If you’d like to create a similar page you can view my page code here: googlemap.txt. (Yep, it’s hacked together. Remember to input your own Google API Key in the Head section.)

3.I set margin:0px to remove the whitespace around the map, trying to make it look less like a web browser control and more like a google control.

Requirements
To use the spreadsheet you’ll need Excel 2003. That’s what I’ve tested it on, for Excel 2002 I believe the “import vba code” is slightly different and minor tweaking will be needed.

To create your own solution you’ll need a Google Developer Key, a Website to host the page, and Excel 2003.

download
Click this link to download the Google Map in Excel Spreadsheet

Update:
A Canadian Google Maps In Excel that works with pre-2003 Excel versions.

Random:
-You can do much more with google maps than just plotting points, I particularly like this example: gMap Workout Tracker

-Microsoft unveiled their new mapping service this week: Virtual Earth (Opens in new window since they have the back button disabled). The satellite imagery in my neighborhood is much nicer than google maps, and the interface has some additional cool tricks. I haven’t tried the virtual earth api yet.


Get LIVE Help
Custom Excel solutions & live Excel help!
► Go to www.ExcelAutomationHelp.com
► Email Now@ExcelAutomationHelp.com
  1. July 30th, 2005 at 14:22
    Reply | Quote | #1

    A very good idea, heck I must get new gear and upgrade though!

    Ossie

  2. August 14th, 2005 at 08:43
    Reply | Quote | #2

    Very cool. How about Word? Can’t wait till they get better data for the rest of the world.

  3. Mark
    August 17th, 2005 at 13:14
    Reply | Quote | #3

    Haven’t tried using gmaps with word, I’m sure it couldn’t be that difficult.

  4. Wolfgang
    September 15th, 2005 at 01:45
    Reply | Quote | #4

    hi mark…

    very cool thang……

    how’bout darmstadt, germany, home of the european space agency??

    best,
    wolfgang

  5. Gyula Gulyas
    September 20th, 2005 at 11:21
    Reply | Quote | #5

    I modified your code and wrote a small demo for Excel 2000/97 users. It does not have the XML parser so I just used the Microsoft XML version 3. It also has the code for Canada using the geocoder.ca web site. Please let me know if you want to have a look.

  6. Mark
    September 20th, 2005 at 17:49
    Reply | Quote | #6

    Hey Gyula,

    Sounds great, you want me to post it or just have a look?

    I currently don’t have any version other than 2003 installed so I don’t know if it will work here(transitioning some things), but I’d give it a whirl and others might possibly be interested.

    Mark

  7. Gyula Gulyas
    September 20th, 2005 at 20:25
    Reply | Quote | #7

    Sure thing. It seems to work on Excel 2002 (i tried on my home PC), as well. I asssume it is because MS has downward compatibility (Microsoft XML version 3 library is intalled by default). I will email it to you tomorrow and feel free to post it. All code is freely available and any suggestions for improvement will be greatly appreciated.

    Your blog is awesome. Keep up the good work!

    Gyula

  8. ELK
    December 20th, 2005 at 08:16
    Reply | Quote | #8

    Mark:

    This might be the solution that I’m looking for. I’m trying to figure out how to mass upload a decent-sized Excel dataset (~6,000 physical addresses) into discrete Google maps for use on a website that I am working on. I need to dig into your work more, but does this seem feasible? I have everything from zip code to individual street address for each entry.

  9. Mark
    December 20th, 2005 at 15:18
    Reply | Quote | #9

    why not just import the spreadsheet into a mysql database and work from there?

  10. RuudS
    October 20th, 2008 at 21:46

    Is it possible to add (like I tried below) something like ‘z= 25′ in the URL to directly get an desired zoom-level?

    WebBrowser1.Navigate (“http://www.automateexcel.com/pl/googlemap.php?lon=” & mylat1 & “&lat=” & mylon1 & “&z=25″)

    Would be nice!

  11. February 11th, 2009 at 20:56

    How are you able to embed this iframe (is that what it is?) into a spreadsheet? I’d like to embed other websites into spreadsheets (like a minibrowser within excel) Thanks

    • jck
      January 12th, 2010 at 22:24

      Roderick-
      To embed this type of frame, go to the Developer Tab in Excel 2007, then choose Insert and select the “More Controls” button under the ActiveX section, followed by selecting “Microsoft Web Explorer”.
      Thanks

  12. Bryan
    February 17th, 2010 at 15:17

    This is exactly what I’m looking for. Here’s a question though. What if I wanted say 5 or 10 dots on my map. Could I refresh the map and it shows multiple dots?

    Also, lets say I have a list of 10 addresses in excel and all these are plotted on the map. Now lets say I filter those 10 addresses. Could the map (once refreshed)change with my newly revised address list?
    Thanks

  13. Belch85
    May 19th, 2010 at 01:44

    Excellent! I’m sure many more people have looked for this kind of API than what have commented – I have a question buddy….

    I can’t find a “geocoder” alternative for Australia – how can we get this to work? We have a list of clients in Excel with an address – and I want to make a macro that pulls the address, gets the image from google maps, and brings it back into excel on a new sheet (Then prints). most of this is easy, and adapting your work shouldn’t be too difficult if i can only find a geocoder for Australia…

    any ideas? Suggestions?

  14. June 11th, 2010 at 19:23

    I consider myself a pretty advanced Excel user. I have developed several vba/Excel solutions but I have to admit I’m feeling like the guy with one eye who’s king amongst the blind when it comes to get GoogleMap info into Excel. And honestly, I don’t understand most of what you’re saying above.

    I downloaded your simple Excel geocode spreadsheet and looked at the macro. Where the hell is the section that copies the Lat/Lon into B8/B9? How are these cells updated? I honestly don’t have a clue.

    But that’s just a minor thing. I really do not know how you are doing it or what I see when I look up the website with the map. I got my Google API key and I believe I need to replace ‘yourkeyhere’ in the html page googlemap.txt with this key.

    What else do I need to do? Probably replace ‘http://www.automateexcel.com/media/XXcomputer.gif\’ and ‘AutomateExcel.com‘ with something, but what? Then I can see ‘//map.centerAndZoom(new GPoint(-84.457741, 37.975192), 4);’ ….. Will this info be updated by the Excel VBA call ‘ActiveWorkbook.XmlMaps(“RDF_Map”).Import URL:=”http://geocoder.us/service/rest/geocode?address=” & sendstring’ ??

    If I had a website called ‘www.stupidme.com’, would I need to replace ‘automateexcel’ with ‘stupidme’? Would I need to create a sub folder ‘/media’? what does the ‘XXcomputer.gif’ look like and what is it for?

    Finally, how do I get the map to my Excel spreadsheet?

    Man and I thought I know a bit about Excel…..

    cheers
    FRANZ

  15. Ark
    April 20th, 2011 at 10:02

    Really good, has anyone tried to get something like this working with the new Bing maps? “M$ docs at there normal level”

  16. Ark
    April 20th, 2011 at 13:48

    You can use Bing maps instead of Geocoder.us to get the longitude and latitude data, I need to work on using the Google API to do the same thing as it would be nice to put up Bing Maps and Googlemaps for the same address.

  17. July 23rd, 2011 at 18:55

    this just made my surveying life much easier today…thank you

  18. 4 trackbacks