(untagged)

Excel Google Maps Add-In

Sotirios Diamantis

0.00/5 (No votes)

8 Oct 2015

You have latitude and longitude coordinates in Excel and you want to display them in Google maps; this simple Excel add-in will let you do just that.

Download source - 23.6 KB

Introduction

You can use Excel add-ins to add on to Excel's out-of-the-box functionality. I find them really helpful, and I want to share a simple Excel add-in here to show you how they work, and how you can create your own.

Using the Code

Download this article's zip to your computer, extract the Excel add-in: Google Maps.xlam, and open it in Excel.

Or tell Excel to load the add-in every time it starts, by selecting Developer > Excel Add-Ins > Browse... > selecting add-in > OK > checking add-in > OK.

If you do not see the Developer tab in Excel, you can show it by selecting File > Options > Customize Ribbon > checking Developer > OK.

If Excel asks you to enable the add-in every time it starts, you can add its location to your trusted locations by selecting File > Options > Trust Center > Trust Center Settings... > Trusted Locations > Add new location... > selecting location > OK > OK.

The add-in adds a Google Maps button to the Add-Ins tab.

To see what it does, copy the following table and paste it into Excel:

Latitude	Longitude	Label
42.3149	-83.0364	Windsor
49.0323	-119.4682	Osoyoos
45.5231	-122.6765	Portland

Highlight the latitudes, longitudes, and labels - but not the titles - and go to the Add-Ins tab and click the Google Maps button.

The add-in generates a Google map JavaScript HTML file in your temp directory and opens it in your default web browser. The Google map contains three markers, one for each of the coordinates in the table. You can hover over each marker to see its label and latitude and longitude.

You can see the VBA code and how it generates the Google map by selecting Developer > Visual Basic > VBAProject (Google Maps.xlam) > Modules > Module1.

The sub with the IRibbonControl object is notified when the Google Maps button is clicked.

'*****************************************************************************
Sub OnGoogleMapsButton(control As IRibbonControl)
'
' This sub is notified when the user clicks the Google Maps button in the
' Add-Ins tab.
'
    Dim Row As Range
    Dim FileName As String
    Dim Label As String
    Dim Latitude As String
    Dim Longitude As String
   
    ' Example; the following cells are in the active sheet:
       
    ' Not highlighted:
   
    '        A          B          C
    '   +----------+------------+---------+
    ' 1 | Latitude | Longitude  | Label   |
    '   +----------+------------+---------+
   
    ' Highlighted:
   
    '   +----------+-----------+----------+
    ' 2 | 42.3149  | -83.0364  | Windsor  |
    '   +----------+-----------+----------+
    ' 3 | 49.0323  | -119.4682 | Osoyoos  |
    '   +----------+-----------+----------+
    ' 4 | 45.5231  | -122.6765 | Portland |
    '   +----------+-----------+----------+

    If Selection.Columns.Count < 2 Then

        MsgBox "You need to highlight at least 2 columns; _
	(1) the latitude and (2) the longitude.", vbCritical + vbOKOnly

        Exit Sub

    End If
   
    If Selection.Columns.Count > 3 Then
       
        MsgBox "You can't highlight more than 3 columns; (1) the latitude, _
	(2) the longitude, and (3) a label.", vbCritical + vbOKOnly
       
        Exit Sub
   
    End If
   
    ' FileName = "C:\Users\Sotirios\AppData\Local\Temp\Google Maps.html"
    FileName = Environ("Temp") & "\Google Maps.html"

    Open FileName For Output As #1
   
    Print #1, "<!DOCTYPE html>"
    Print #1, "<html>"
    Print #1, "  <head>"
    Print #1, "    <meta name=" + Chr$(34) + "viewport" + Chr$(34) + _
	" content=" + Chr$(34) + "initial-scale=1.0, user-scalable=no" + Chr$(34) + ">"
    Print #1, "    <meta charset=" + Chr$(34) + "utf-8" + Chr$(34) + ">"
    Print #1, "    <title>Google Maps</title>"
    Print #1, "    <style>"
    Print #1, "      html, body, #map-canvas"
    Print #1, "      {"
    Print #1, "        height: 100%;"
    Print #1, "        margin: 0px;"
    Print #1, "        padding: 0px"
    Print #1, "      }"
    Print #1, "    </style>"
    Print #1, "    <script src=" + Chr$(34) + _
	"https://maps.googleapis.com/maps/api/js?v=3.exp&signed_in=true" + Chr$(34) + "></script>"
    Print #1, "    <script>"
    Print #1, ""
    Print #1, "function initialize()"
    Print #1, "{"
    Print #1, "  var mapOptions ="
    Print #1, "  {"
    Print #1, "    zoom: 2,"
    Print #1, "    center: new google.maps.LatLng(0, 0)"
    Print #1, "  };"
    Print #1, ""
    Print #1, "  var map = new google.maps.Map(document.getElementById('map-canvas'), mapOptions);"
       
    For Each Row In Selection.Rows
   
        ' Latitude = "42.3149"
        Latitude = Trim(Row.Cells(, 1).Text)
       
        If IsNumeric(Latitude) = False Then
       
            Row.Cells(, 1).Activate
           
            MsgBox "The latitude (in the active cell) needs to be numeric.", vbCritical + vbOKOnly
           
            Close #1
           
            Exit Sub
       
        End If
       
        ' Longitude = "-83.0364"
        Longitude = Trim(Row.Cells(, 2).Text)
       
        If IsNumeric(Longitude) = False Then
       
            Row.Cells(, 2).Activate
           
            MsgBox "The longitude (in the active cell) needs to be numeric.", vbCritical + vbOKOnly
           
            Close #1
           
            Exit Sub
       
        End If
       
        If Selection.Columns.Count = 3 Then
       
            ' Label = "Windsor"
            Label = Trim(Row.Cells(, 3).Text)
           
        Else
       
            Label = "Marker " + CStr(Row.Row)
       
        End If
       
        Print #1, ""
        Print #1, "  var marker" + CStr(Row.Row) + "= new google.maps.Marker("
        Print #1, "  {"
        Print #1, "    position: new google.maps.LatLng(" + Latitude + ", " + Longitude + "),"
        Print #1, "    title: " + Chr$(34) + Label + ": (" + Latitude + ", " + Longitude + ")" + _
	"\nDrag this marker to get the latitude and longitude at a different location." + _
	Chr$(34) + ","
        Print #1, "    draggable: true,"
        Print #1, "    map: map"
        Print #1, "  });"
        Print #1, ""
        Print #1, "  google.maps.event.addListener(marker" + CStr(Row.Row) + _
		", 'dragend', function(event)"
        Print #1, "  {"
        Print #1, "    var Title = marker" + CStr(Row.Row) + ".getTitle();"
        Print #1, "    var SubStrings = Title.split(" + Chr$(34) + "\n" + Chr$(34) + ");"
        Print #1, "    marker" + CStr(Row.Row) + ".setTitle(SubStrings[0] + " + _
	Chr$(34) + "\n" + Chr$(34) + " + "; Chr$(34) + _
	"The latitude and longitude at this location is: " + Chr$(34) + " + marker" + _
	CStr(Row.Row) + ".getPosition().toString());"
        Print #1, "  });"
   
    Next Row
   
    Print #1, "}"
    Print #1, ""
    Print #1, "google.maps.event.addDomListener(window, 'load', initialize);"
    Print #1, ""
    Print #1, "    </script>"
    Print #1, "  </head>"
    Print #1, "  <body>"
    Print #1, "    <div id=" + Chr$(34) + "map-canvas" + Chr$(34) + "></div>"
    Print #1, "  </body>"
    Print #1, "</html>"
   
    Close #1
   
    ActiveWorkbook.FollowHyperlink Address:=FileName, NewWindow:=True
   
End Sub

You can see how the add-in adds the Google Maps button to the Add-Ins tab, and how it notifies the VBA sub when its clicked, by opening the add-in in Custom UI Editor For Microsoft Office.

<customUI xmlns="http://schemas.microsoft.com/office/2009/07/customui">
 <ribbon>
  <tabs>
   <tab idMso="TabAddIns">
    <group id="GroupMaps" label="Maps">
     <button id="ButtonGoogleMaps" image="Google-Maps" size="large" _
	label="Google" screentip="Show highlighted latitudes and longitudes in Google Maps" _
	supertip="The latitude should be in the first highlighted column, the longitude in the second, _
	and a label (if desired) in the third." onAction="OnGoogleMapsButton" />
    </group>
   </tab>
  </tabs>
 </ribbon>
</customUI>

"image=" tells Excel what icon to display for the Google Maps button; in this case it is a .png file from IconArchive and it was added to the add-in using the Custom UI Editor For Microsoft Office.

"onAction=" tells Excel what VBA sub to notify when the Google Maps button is clicked.

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here