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.

Sub ConnectToUrl()
If bPing("74.125.79.94") Then 'Ping the IP address of google.co.uk.
    With ActiveSheet.WebBrowser1
        .Visible = True
        .Silent = True
        .Navigate ActiveSheet.Range("$B$4").Value
    End With
Else
    ' Hide the browser object to show your own error message
    ActiveSheet.WebBrowser1.Visible = False
End If
End Sub

Function bPing(sHost) As Boolean
Dim i As Integer
Dim oPing As Object, oRetStatus As Object
For i = 1 To 2 ' Attempt up to twice to allow for connection hiccups.
    Set oPing = GetObject("winmgmts:{impersonationLevel=impersonate}").ExecQuery _
            ("select * from Win32_PingStatus where address = '" & sHost & _
            "' AND Timeout = 100")
    For Each oRetStatus In oPing
        If IsNull(oRetStatus.statuscode) Or oRetStatus.statuscode <> 0 Then
            bPing = False
        Else
            bPing = True
            Exit Function
        End If
    Next
Next
End Function