Click here to Skip to main content
15,115,261 members
Articles / Web Development / HTML
Tip/Trick
Posted 8 Oct 2015

Stats

50.1K views
2.4K downloads
15 bookmarked

Excel Google Maps Add-In

Rate me:
Please Sign up or sign in to vote.
4.50/5 (6 votes)
8 Oct 2015CPOL2 min read
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.

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.

Image 1

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.

VB.NET
'*****************************************************************************
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.

HTML
<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, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

No Biography provided

Comments and Discussions

 
QuestionAre the links / code sample still current? Pin
madurao23-Oct-21 8:37
Membermadurao23-Oct-21 8:37 
QuestionGoogle Maps "For Development Purpose Only" Pin
Member 143591061-Jun-20 17:01
MemberMember 143591061-Jun-20 17:01 
QuestionGoogle Maps code sample- Google Maps showing "for developer use only" Pin
Member 1469836226-Dec-19 12:29
MemberMember 1469836226-Dec-19 12:29 
Questionnot working Pin
Member 144860209-Jun-19 2:51
MemberMember 144860209-Jun-19 2:51 
Questionusing variables in the google map parameters - map centering Pin
Member 131116817-Apr-17 10:58
MemberMember 131116817-Apr-17 10:58 
QuestionLabels Pin
Member 131077825-Apr-17 8:01
MemberMember 131077825-Apr-17 8:01 
PraiseImpresionante! Pin
almagroriano196430-Sep-16 11:06
Memberalmagroriano196430-Sep-16 11:06 
Questioncompatibilidad Pin
almagroriano196430-Sep-16 10:37
Memberalmagroriano196430-Sep-16 10:37 
GeneralThanks! Pin
nic.fer12-Dec-15 1:44
Membernic.fer12-Dec-15 1:44 
GeneralCool! Pin
Tokinabo12-Oct-15 7:57
professionalTokinabo12-Oct-15 7:57 
Thanks for sharing this nice stuff!

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.