Add a Static Google Map to a Worksheet

Adding a static Google map to a worksheet requires only a few lines of code, an empty rectangular “shape” placed on the sheet and an account with Google to access their Maps API. You DO NOT need to find and add some obscure web browser control which may or may not work on your version of Excel (I am using Office 365 and this code was written on Excel 16, 64 bit.)

The entire Google Maps Platform is complex and will not be explained here. This is simply a  tutorial on placing a static map (without zoom, street view or travel direction capabilities) on an Excel worksheet.  Adding these other features to your project will require some more effort and possibly the Webbrowser ActiveX control, which may or may not work. Most likely it will also come at an expense…Google will ask you for a credit card number during the registration process.

The following image is of an Excel Dashboard which was developed for a building estimator workbook. At the lower right you will see the static Google map. It is created dynamically from the Job Address of the estimate each time the Dashboard in opened.

Adding the map

The first thing to do is to get your own Google Platform key. This key must be sent at the end of the URL which you will use to get the static map image. To get your own key go here. Do not get overwhelmed by the different products and pricing simply get one. Static map image retrieval is FREE.

The second step is to insert a rectangle with No Fill on the worksheet. From the Ribbon Insert menu, select Shapes and then the Rectangle (see the following image.) Right click on the inserted rectangle to select Format Shape from the bottom of the context menu. Then Select No Fill. Removing the Fill color isn’t necessary as the Google map will cover any fill color, but it is good housekeeping in case there is an error and No image is returned.

The Shape “name” can be found in Excel’s Name box at the left of the formula bar. You can change it if you like, but you need to know it for the map insertion code. Here it is shown as “GMaps_1”. Now all that is left to do is write a little VBA code.

The final step is to write a macro (a Sub procedure) and run it when you want the map populated. If you are basing the mapped address on the entries in a cell (or two) you may want to add a check for valid entries so the map image does not come back wrong. In the following code, you can see that I have taken the values in two cells (Street address and City, State, Zip Code) concatenated them, and added some plus (+) signs. This, too, isn’t mandatory as spaces are fine with Google (Google will add the + signs when you run the URL.

Sub insertMap()
Dim gUrl, strAddress As String

    ' Get the address and clean it up
    strAddress = Trim(Sheet1.Range("C9") & " " & Sheet1.Range("C10"))
    Sheet9.Range("N22") = strAddress
    strAddress = Replace(strAddress, ",", "")
    strAddress = Replace(strAddress, " ", "+")

    ' Set up the google static map API URL (https://developers.google.com/maps/documentation/maps-static/dev-guide)
    gUrl = "https://maps.googleapis.com/maps/api/staticmap?center=" & strAddress & ",0&markers=|" & strAddress & "|&zoom=17&size=640x640&key=YOUR GOOGLE KEY GOES HERE"
    Sheet9.Shapes("GMaps_1").Fill.UserPicture gUrl
        
End Sub

The last two lines of code are all that is required to retrieve and insert the static map into the rectangle shape on the worksheet. Of course, at the end of the URL, the phrase “YOUR GOOGLE KEY GOES HERE” should be replaced by YOUR Google API key.  The Static Map Documentation explains all of the parameters used in the URL.webpage. The variable “strAddress” is for my specific use, but it show how you can add variables to the URL string.

7 thoughts on “Add a Static Google Map to a Worksheet

  1. I can’t get this line to work. I get and error that says “Method ‘UserPicture’ of object ‘Fillformat’ failed. Can you help?

    Sheet9.Shapes(“GMaps_1”).Fill.UserPicture gUrl

  2. Hi Rick

    Thanks for this, I changed the code slightly and this worked using ” & LCase(Range(“BB21″).Value) & ”

    Also its important that you remember to enable billing for the API.

    Sub Rectangle8_Click()

    Dim gUrl
    Dim strAddress As String

    ‘ Get the address and clean it up
    strAddress = Trim(ActiveSheet.Range(“S21″) & ” ” & ActiveSheet.Range(“S23″) & ” ” & ActiveSheet.Range(“S25″) & ” ” & ActiveSheet.Range(“S27″) & ” ” & ActiveSheet.Range(“S29”))
    ActiveSheet.Range(“BB21”) = strAddress
    strAddress = Replace(strAddress, “,”, “”)
    strAddress = Replace(strAddress, ” “, “+”)

    ‘ Set up the google static map API URL (https://developers.google.com/maps/documentation/maps-static/dev-guide)
    gUrl = “https://maps.googleapis.com/maps/api/staticmap?center=” & LCase(Range(“BB21”).Value) & “&zoom=16&size=640×640&key=AIzaSyA77bBCtfB6uD3jLzNnEZNh6Hi3Wi0mnwI”
    ActiveSheet.Shapes(“GMaps_1”).Fill.UserPicture gUrl

    End Sub

    Thanks for your help!

    • I am not sure what is not working for you. The code shown on the post works, and still works for me. If you are having trouble with the address and you concatenating it, I would look for no spaces where they should be or unwanted characters. If you copy my example it should be relatively simple to find an error.

Leave a Reply

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.