To top off the recent Gmaps and Excel posts, I thought I’d show you how to embed a map in a worksheet. This involves three main steps.

1) Inserting the web browser object
The first step is really simple. Just go to the developer tab on the ribbon and click the “More controls” icon – it’s the one that looks like a crossed hammer and spanner/wrench. Select the Microsoft Web Browser from the list of ActiveX controls, and then click and drag on the worksheet to place your web browser object.

2) Generating the URL to show your desired map
Probably the simplest thing to show in your browser is an embedded map with a postcode. This is the easiest way to show a marker on the map.
The simplest format for the URL is as follows:

http://maps.google.co.uk/maps?q=your_postcode_goes _here&output=embed&iwloc=near

Once your map is showing, you can interact with it just like a regular Google map page, zooming in and switching between map modes. You can even go into Street View or Google Earth.
There are loads of other parameters you can add to fine tune the results but this is as simple as it gets. You can find more info here, here or here for things like adjusting zoom level, showing hybrid maps and so on.

The limitation of this approach is it only allows you to show one marker. If you want to show more than one you can display a static map, at the cost of no longer being able to interact with it. The API for this is pretty simple. The basic format of the URL is as follows:

http://maps.google.com/maps/api/staticmap?&center=your_location&zoom=19&size=525x330&maptype=roadmap&
markers=color:red|label:A|your_latitude,your_longitude&markers=color:green|label:B|your_latitude,your_longitude&
sensor=false

Again, there’s plenty you can do to jazz up the display including replacing roadmap with satellite or hybrid.

3) Adding the VBA code to navigate to your URL
This might seem to be the most complicated part if you’re not familiar with VBA, but it’s actually pretty simple and really worth having a go at. Adding a link to a webpage such as a Google map can make a really big difference to the appearance of your spreadsheet.

There are two parts to the code, a sub to control and a function to ping Google and make sure you have a live connection to the internet. There’s nothing worse than showing your fancy new dashboard to a client, only to see an Internet Explorer error page. Checking the connection first means that you can hide the web browser to reveal your own error image sitting behind it.

Jamie Bull | jamiebull1@gmail.com

Related Posts

WiGLE is a popular platform which can be used for finding the location of a device using the names of WiFi networks in its vicinity. I’ve written about this before, and wrote some Python code to interact with their API. This API has since been retired and replaced with a new one, as of December […]

Just a quick post to point out a couple of really useful tools. The first is a web-based tool for finding weather files for a location of interest. It’s similar to the Excel EPW finder tool we created a few years back, but much more modern looking. It is however missing a few of the […]

Eppy is a really useful library which I’ve written about several times, since before I really had anything to offer in terms of contributing code. Over the past year or so though, I’ve started to contribute back some of the changes and additions I’ve made while using eppy on academic and commercial projects. This post […]

Leave a Reply

Your email address will not be published. Required fields are marked *