Google Static Maps API in Excel

The Google Static Maps API may be leveraged in Excel wherever you want local street maps of your area. Here I'll review some basic techniques for building links that let you display maps from Excel and VBA.

Note that there are numerous ways we can display maps using Google's API. This is just one approach.

The Street Address Approach

Let's assume we have a street address in A1, city in B1, state abbreviation in C1 and zip code in D1. I want my map to display that address and center it on my map.

I've named each cell, so it really doesn't matter where on the worksheet it's located, and it will make my formulas easier to read:

A1 is named StreetNo
B1 is named City
C1 is named State
D1 is named ZipCode

address for maps API
(FYI this is the address for the Manhattan office of the City Clerk.)

The Hyperlink Formula

Let's start with the final formula, then work out the parameters.

=HYPERLINK("http://maps.google.com/maps/api/staticmap?center="&SUBSTITUTE(StreetNo," ","+")&","&SUBSTITUTE(City," ","+")&","&State&","&ZipCode&"&zoom=18&size=640×640&maptype=hybrid&sensor=false","Map")

This example uses the HYPERLINK function to display a friendly name in the cell.

Let's start with the base URL: http://maps.google.com/maps/api/staticmap?

The first parameter is center, which defines the center of the map. In this example, I use the street address (spaces replaced with '+'). You may also use latitude/longitude as well.

The second parameter, zoom, determines the zoom level of the map. Zoom level can be any number from 0 through 21 (inclusive). I use 18 to create a decent street view.

The size parameter defines the size of the resulting image. The maximum size is 640×640. I want my map as large as possible because I have to print it.

The maptype parameter has four different possible types: roadmap, satellite, hybrid, and terrain. See MapTypes for sample images showing how each different map type looks.

The sensor parameter should generally be set to 'false', unless you are using the Google Maps API with mobile applications or other devices that detect the user's location using GPS.

This is the absolute simplest map you can create; there are many more options you can use, such as drawing lines and custom markers, which I won't get into here. Needless to say, I could spend all day coming up with uses for this API.

Using VBA

Here's a sample VBA function that takes the street address, zoom level, map size, type and image format and returns the URL as a string. I added the image format as a parameter because if you use this function to download the image to a file (in order to load it in an Image Control), it will need to be a format other than the default (PNG). In that case, you'll need to be able to specify a different format.

Function GetGoogleMap(streetAddress As String, city As String, state As stateAbbr, _
                      zipCode As String, zoomLevel As Long, _
                      Optional size As String = "640x640", _
                      Optional mapType As mapType = roadmap, _
                      Optional imageFormat As imageFormat = PNG) As String

Dim scrubbedAddress As String
Dim URL As String

  ' scrub address of spaces
  scrubbedAddress = _
  EscapeString(streetAddress & "," & city & "," & GetStateAbbr(state) & "," & zipCode)

  ' check for invalid state parameter
  If InStr(scrubbedAddress, "unknown") > 0 Then
    Exit Function
  End If

  ' zoom level must be 0 - 21
  Select Case zoomLevel
    Case Is < 0, Is > 21
      If zoomLevel < 0 Then zoomLevel = zoomLevel + VBA.Abs(zoomLevel)
      If zoomLevel > 21 Then zoomLevel = zoomLevel - (zoomLevel - 21)
  End Select

  ' how to validate size parameter?

  URL = BASE_URL & "center=" & scrubbedAddress & "&zoom=" & zoomLevel & _
        "&size=" & size & "&maptype=" & GetMapType(mapType) & "&format=" & _
        GetImageFormat(imageFormat) & "&sensor=false"

  GetGoogleMap = URL
End Function

The address fields must be escaped with plus signs '+' so I wrote a function to do that exclusively:

Function EscapeString(str As String) As String
' replace spaces in string with plus signs for passing to API
  EscapeString = Replace(str, " ", "+")
End Function

The street address is not validated (that's a different API), and I couldn't figure out a way to validate the image size, since there are so many possibilities. You might want to limit the number of possible image sizes, in which case you could set up an enumeration to do so. In my example, the largest possible size (640 x 640 pixels) is the default because I want it as large as possible on the printed page.

Since the zoom level must be a number between 0 and 21, we simply force any outliers to those values. I'd welcome anyone who could produce something shorter than the If-ElseIf statement I used.

There are helper functions used in the above function which I won't print here. However, I've created a workbook with the GetGoogleMap function, as well as all of the helper functions and enumerations it needs to operate. It also includes the HYPERLINK function formula.

Download sample workbook (Excel 2003)
Download sample workbook (Excel 2007)

Sample Usage

Sub TestGetMap()
  MsgBox GetGoogleMap("141 Worth Street", "New York", NEW_YORK, "10013", 18)
End Sub

This displays the following:

Static Map hyperlink

If I pass this string to an instance of Internet Explorer, I'll see my map:

Sub GetIEMap()

Dim ie As Object

Set ie = GetIE

With ie
  .navigate GetGoogleMap("141 Worth Street", "New York", NEW_YORK, "10013", 18)
  ' loop while page is busy
  Do While .ReadyState <> 4 ' READYSTATE_COMPLETE
    DoEvents
  Loop
  ' display map in IE window
  .Visible = True
  ' print ?
  ' .ExecWB 6, 2
End With

End Sub

The GetIE function may be downloaded from Automate Internet Explorer.

UDF Version of the Function

The GetGoogleMap function can be used as a UDF, but it does require some recoding because most workbooks will list state abbreviations as Strings, not numbers. Using numbers to represent state abbreviations is clumsy outside of the VBA environment. So we can rewrite it like this:

Function GetGoogleMap_UDF(streetAddress As String, city As String, state As String, _
                      zipCode As String, zoomLevel As Long, _
                      Optional size As String = "640x640", _
                      Optional mapType As String, _
                      Optional imageFormat As String) As String

Dim scrubbedAddress As String
Dim URL As String

  ' scrub address of spaces
  scrubbedAddress = _
  EscapeString(streetAddress & "," & city & "," & state & "," & zipCode)

  ' zoom level must be 0 - 21
  Select Case zoomLevel
    Case Is < 0, Is > 21
      If zoomLevel < 0 Then zoomLevel = zoomLevel + VBA.Abs(zoomLevel)
      If zoomLevel > 21 Then zoomLevel = zoomLevel - (zoomLevel - 21)
  End Select

  ' how to validate size parameter?

  URL = BASE_URL & "center=" & scrubbedAddress & "&zoom=" & zoomLevel & _
        "&size=" & size & "&maptype=" & mapType & "&format=" & _
        imageFormat & "&sensor=false"

  GetGoogleMap = URL
End Function

Then we can use the HYPERLINK formula like this:

=HYPERLINK(GetGoogleMap_UDF(StreetNo,City,State,ZipCode,18,,"hybrid"),"Map")

Note that no validation is now performed on the state abbreviation, but we didn't validate the address in the previous function either, so I don't feel so bad.

Other Considerations

If you are planning on downloading the image and displaying it in an Image control, you'll want to specify the format parameter as 'gif' or 'jpg' instead of the default, which is 'png'. Image controls won't accept PNG files. I didn't test this, however.

Download sample workbook (Excel 2003)
Download sample workbook (Excel 2007)

About JP

I'm just an average guy who writes VBA code for a living. This is my personal blog. Excel and Outlook are my thing, with a sprinkle of Access and Word here and there. Follow this space to learn more about VBA. Keep Reading »



Share This Article:

Share and bookmark this articledelicious buttonfacebook buttonlinkedin buttonstumbleupon buttontwitter button

comment bubble 1 Comment(s) on Google Static Maps API in Excel:

  1. Using google maps, bing,ovi and yahoo, Heres how to do geocoding, add markers, and create data driven mapping applications straight out of excel. Taking it a little further than the example here, it shows how to embed google visualisations of your excel data in maps info windows as well.

    http://ramblings.mcpher.com/Home/excelquirks/getmaps

    Bruce

This article is closed to any future comments.
learn excel dashboards