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?¢er=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 useful […]

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 is […]

29 Comments on “Put a Google map in your spreadsheet”

  • Olli S says:

    Hello Jamie,

    thanks a lot for your information on this website – they helped me a lot.
    I do have a question and hope you can help me.

    I have set some Markers from Excel in a Static Google Map, you can see them in the left picture (your information worked fine for me – thanks).
    Is there also a possibility to add Informations to the Markers like the red or the yellow one?

    http://imageshack.us/photo/my-images/534/tm8b.jpg/

    Would be great.

    Thanks a lot in advance and greetings from Austria.

    Olli

    • Jamie Bull says:

      Hi Olli, Glad it’s been useful to you. Sadly though, as far as I know there’s no way of adding any information other than the label.

      • Olly Horne says:

        I’m struggling with this can you help?

        I have pasted the box into my worksheet but where do I put the url to make it a map?

        Thanks
        Olly

  • Olli S says:

    Hi Jamie,

    thanks for your Information, I do have some other questions and hope you can answer them too:

    Here’s a sample from my workbook:

    https://maps.google.at/maps/api/staticmap?&zoom=10&size=2048×2048&maptype=hybrid&markers=color:red|label:A|46.6216616,14.3426178|&markers=color:green|label:B|46.7039486,14.8695743|&markers=color:red|label:C|46.9613699,14.4004526|&markers=color:green|label:D|46.9578183,14.4062557|&markers=color:orange|label:E|46.9514963,14.4114647|&markers=color:green|label:F|46.8024693,14.4031975|&markers=color:red|label:G|46.8017711,14.4022119|&markers=color:blue|label:H|46.7673013,14.3629055|&markers=color:red|label:I|46.7510000,14.3800875|&markers=color:green|label:J|46.7484118,14.3829075&sensor=false

    Is it possible to Center the static Image in a way, that all markers are always visible?
    At the moment I have to adjust the zoom Level manually.

    And another Problem i do have is the bulk of some Icons, like on top with C / D / E, is there a possibility to choose other Icons or to zoom / Center to a specifiv part of the static Image?

    Thanks a lot and Kind regards

    Olli

  • Olli S says:

    Hello Jamie,

    I just saw that the URL I have posted got crippled somewhere…
    Here is the picture of the Static Map I have generated:

    http://imageshack.us/photo/my-images/191/5nvq.png/

    I have read, that there is a Limit ox 640×640 Pixel for free Use within Google Maps,
    but that there is a possibility to get 2048×2048 Pixel if you have a Premium Account…

    http://code.google.com/p/gmaps-api-issues/issues/detail?id=678
    https://developers.google.com/maps/documentation/staticmaps/#Imagesizes

    But how do I get such an Account, and how do i embed this information within Excel?

    Hope you can help me.

    Kind regards and thanks a lot

    Olli

    • Jamie Bull says:

      The size limit is news to me. I’ve never tried to use a larger static map. To get a Business API license you’d need to get in touch with Google Enterprise via this form. Once you have a Business API key then you can use that in Excel the same as in any other application. I’ve not used it myself but instructions are here.

  • Rob Grant says:

    Great article, thanks for sharing, it’s saved me loads of time already!

    But one question….is it possible to include interactive Streetview in Excel?

    • Jamie Bull says:

      If you have an embedded browser then Streetview should work. I’ve not tried it though so can’t be sure. From memory the Streetview URL parameters are fairly complicated so it could be quite a bit of work.

  • Val says:

    I have followed the three steps but for some reason, when I run the macro I get a blank image. Now, let me tell you exactly what my situation is:
    – Sheet1 includes a list of physical addresses as well as other information (client name etc). The address is entered as follows: column B – number, column C – street, column D – city, column E – zip code
    – Sheet 2 generates a cover page. For example, when I enter the client number, it automatically displays the client’s address as well as other information
    – Sheet 2 also displays a small map of the location (400 x 400) that I can insert manually by using Hypersnap. I do this manually (go to maps.google.com, enter the address, get the map, copy it with Hypersnap and then paste it in Excel). This is time consuming. What I want is this: I want the map to be displayed automatically every time I enter the client number

    I have no VBA knowledge. When I copied the codes, I did this:
    1. created a macro
    2. deleted the “sub … endsub” thing that is automatically written when you create the macro
    3. inserted the codes from this page
    4. stopped recording
    5. ran the macro
    6. got an empty cell
    The MS Web Browsing control was active ( I’ve followed exactly what you’ve said)

    Thanks!

  • Carl says:

    Hi Jamie,

    Thanks for your sharing, finally I’ve learned how to create one after searching different tutorials.

    I was wondering if you know the code to put different markers with different colors to make a simple GIS analysis.

    My current URL is like this: =”http://maps.google.com/maps?saddr=”&B1&”&output=embed&daddr=”&B2

    I will be really appreciated if you could help.

    cheers,

    Carl, Taiwan

  • Dmitrij says:

    Hi Jamie,

    Thanks for the great article. However, I’m stuck right on the first step (I presume it because I’m too dummy for excel coding and stuff like that). Anyways, picture is attached. Could you help me out? http://postimg.org/image/kmm4zctpr/

    Thanks in advance.

    Regards,
    Dmitrij

  • Elkan says:

    I know this is an old thread already but I am still going to try. Right now, google map uses a newer version that involves iframe and I have no idea how to write the URL link that incorporates the iframe for the embed google map to work.

    • Jamie Bull says:

      Hi Elkan. I’ve not looked at this function for quite some time, and it might be a while before i get a chance. I’d be interested to hear if you do manage to figure it out, and will certainly let you know if I do get a chance to look at it myself.

      • Dora says:

        Hi, I have the same issue as Elkan. I’d like to have the regular view with zooming etc but it requires the iframe. If I remove
        the embed part from the URL, it works but the left side bar / search bar from google will also be displayed on the map
        which I don’t want. Please let me know if you have a solution to sort out this problem.

  • rio says:

    hi jamie, nice article before.
    i wanna ask, can i do this (add google map in excel) with coordinates include in excel file, and can i do this in server side? because as far i know, server doesn’t accept excel object. thanks for your help.

    • Jamie Bull says:

      Sorry, I’m not sure what you’re asking here. Are you trying to show a map in a browser? Because if so there’s no need to use Excel at all, just whatever serverside js library you’re most comfortable with.

      • rio says:

        i mean, i do upload excel file with coordinates inside (longitude and latitude), to server, then i can download that file with map inside with match with the coordinates. can i do that jamie? thanks.

        • Jamie Bull says:

          I don’t see why not. You can save the lat/long data in a CSV file and then make a web query to that CSV file from the Excel file with the map in it.

  • David J says:

    Hi Jamie,

    I want to be able to work out the distance between two postcodes, I’ll have a big list of postcodes to compare. I want to be able to work out the distance between each one. Is there a way of getting such a loop created?

    • Jamie Bull says:

      Hi Davie,

      You might find a Python implementation better suited to a large list of distances than VBA/Excel. There’s also the question of rate limiting to consider. How many postcodes do you have?

  • Dieter DG says:

    Hi Jamie,

    Nicely done. I have tried to rebuild a sortlike document, which is working fine in office 2007, but does not want to work in office 2016. Do you have any idea why this is the case?

    TIA

    • Jamie Bull says:

      I suspect your problem is that the webbrowser ActiveX control is not available in the current version of Excel. You should still be able to show a static map though.

  • Catherine Leigh says:

    Hello …I wonder if you can PLEASE help me. I have to work out daily business mileages for several business operatives. They supply me the post codes for where they travel to , to an from daily. I am currently logging in and out of AA Route planner to calculate mileage but this is taking me hours …is there any way I can simply insert a formula on my excel spreadsheet ?

Leave a Reply

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