We’ve already looked at how to geoCode addresses using various APIs. Now lets look at using Google Maps to put markers on a Map using an Excel worksheet as input. As usual you can find the completed example in the googleMapping.xlsm download. Before running this please check the parameter worksheet and change the filename value for the place that the application will generate a web page to something relevant for your machine. Look for the filename in the Marker html section of the Parameter Worksheet.
Putting markers on a map for our ‘Customer Sheet’
If you’ve been following along with the geoCoding examples you will already be familiar with our test data, which we’ve now already geocoded from input addresses of various levels of accuracy. You can also customize the markers and add heatmaps and circles – see Overlaying circles and heatmaps
The objective of this section is to plot this sheet on a map, with a marker for each row. In addition we are also going to have a popup box that will show the customer name and the full address. The final map using our test data looks like this.
Parameters
Like many of the examples on this site, we are using parameters to drive the behavior of the code. Many of the parameters used in the geoCoding examples are used, but we have a couple of new sections in the parameter worksheet. 4 pieces of data are needed for placing markers on the map are given under the ‘Markers’ parameter block. This instructs the application which columns in the table contain these values.
Preparing the Content
The title Marker Parameter specifies the column containing the value you want shown in reaction to a mouse hover over the map marker. In this case, we want to show the customer name. The content marker parameter specifies the column containing the value you want to show in the pop-up box in reaction to a click on the map marker. In this case, we want to create a description of the customer and his address, and we can use html to construct it if necessary. You cab see that I’ve added a content field in our example application that looks like the below, but you can of course put whatever you want in there. A blank content field will mean that you don’t need a pop up box for that marker.
="<b>" & B2 & "</b><br>" & SUBSTITUTE(L2,",","<br>") & "<br>"
which translates to
<b>Acme</b><br>1600 Amphitheatre Pkwy<br> Mountain View<br> CA 94043<br> USA<br>
Customizing the generated web page
The application creates , and displays, a web page in the place you specify in the filename parameter of the Marker Html section of the Parameter WorkSheet.
The other parameters in this section contain the Code needed to generate this web page. Header, main and functions are largely javaScript and are responsible for interpreting the Excel data and plotting it on the map. You can change these if you want of course, but only if you want to change the functionality of the application. The body section however is likely going to need to be customized to design whatever web page you want to create. Right now it just looks like this
Quick Links Excel and Google Mapping Integration <a href="http://ramblings.mcpher.com"> ramblings.mcpher.com</a>
As long as you retain the <body onload=”initialize()”> (load up the map when the page loads) and <div id=”map_canvas”></div> (the place on the web page to contain the map), you can tailor this body parameter as as much as you like.
Compare Google Maps to others
I’ve implemented this exact same thing on other APIs as below. Take a look to compare. My recommendation is to stick with Google Maps.
- Adding markers to Bing Maps from Excel
- Adding markers to Google Maps from Excel
- Adding markers to Ovi Maps from Excel
- Adding markers to Yahoo Maps from Excel
- Create KML file from Excel Data
The generated html file
When you execute the code for marking up the customer worksheet, an html file is generated in the location you have specified and IE is kicked off to display it. (You may get a security warning about running local content on IE depending on your settings). The generated file from our example application looks like this. It is just the head, main, body and functions code from the Parameter sheet, plus the function mcpherDataPopulate(), which is generated at run time and contains the Excel data that will be displayed on the map. By default, the map will be displayed at zoom level 2, but you can change that with a Url parameter, for example (we use file:// instead of http:// because the file has been generated locally, but you may want to host it on a web site, or use localhost if you are running a server on your pc) file:///D:/googlechart/googleMarkerGenerated.html?zoom=5
Finally, the map will be centered around the first row in your data and will be a standard google Map. If you wanted to change that default (for example to be centered around your head office, with a satellite view), then you would need to change the code section below of the main parameter
var myOptions = {
center: new google.maps.LatLng(mcpherData.cJobject[0].lat,
mcpherData.cJobject[0].lng),
mapTypeId: google.maps.MapTypeId.ROADMAP
Generated html file contents
<!DOCTYPE html>
<html>
<head>
<meta name="viewport" content="initial-scale=1.0, user-scalable=no" />
<style type="text/css">
html { height: 100% }
body {
height: 100%;
margin: 0;
padding: 0 ;
color: Gray ;
background-color: WhiteSmoke;
}
#map_canvas { height: 100%}
.mcquicklink {
margin-left: 20px;
margin-right: 20px;
display: inline;
font-family: sans-serif;
float: right;
}
.mccategory {
margin-left: 20px;
margin-right: 20px;
display: inline;
font-family: sans-serif;
float: right;
}
.mcheading {
display: inline;
font-family: sans-serif;
}
</style>
</head>
<body onload="initialize()">
Quick Links
Excel and Google Mapping Integration</b>
<a href="https://ramblings.mcpher.com">
ramblings.mcpher.com</a>
</body>
The Vba code
Most of the code used here is already covered in Creating Google Maps Layers from Excel Data, json, Data Manipulation Classes, and Google Visualization but here is the specific code for creating the html file from the Customer Worksheet.
' This will take the geocoded data and mark it
Option Explicit
'for more about this
' https://ramblings.mcpher.com/classes/datamanip/ses
'to contact me
' click on one of the icon above
'reuse of code
' https://ramblings.mcpher.com/reusing-code-from-this-site/
Public Sub genericMarking(paramName As String, markerHtml As String, _
Optional eOutput As eOutputMarkers = eOutputHtml)
Dim dSets As cDataSets, dc As cCell, job As cJobject, fName As String
Dim dr As cDataRow, vc As cCell, a As Variant, i As Long
Set dSets = dSetsSetup(paramName)
If dSets Is Nothing Then Exit Sub
' check that we have the required marker fields
With dSets
For Each dc In .dataSet(cMarkers).column("Column Name").rows
If .dataSet(cMarkers).isCellTrue(dc.row, "required") Then
With .dataSet(cMaster).headingRow
If Not .validate(True, dc.toString) Then Exit Sub
End With
End If
Next dc
End With
' we have it all now create a job
Set job = New cJobject
With job.init(Nothing)
' add framework/control parameters
With .add("framework").add("control")
For Each dr In dSets.dataSet(cControl).rows
.add dr.value(cControl), dr.value(cControlValue)
Next dr
End With
With .add("cJobject").addArray
For Each dr In dSets.dataSet(cMaster).rows
With .add
For Each dc In dSets.dataSet(cMarkers).column(cMarkers).rows
If Not dSets.dataSet(cMarkers).isCellTrue(dc.row, "array") Then
Set vc = dr.cell(dc.parent.cell("Column Name").toString)
If Not vc Is Nothing Then
.add dc.toString, vc.toString
End If
Else
a = Split(dc.parent.cell("Column Name").toString, ",")
With .add(dc.toString).addArray
For i = LBound(a) To UBound(a)
Set vc = dr.cell(CStr(a(i)))
If Not vc Is Nothing Then
.add.add CStr(a(i)), vc.toString
End If
Next i
End With
End If
Next dc
End With
Next dr
End With
End With
' now create the html file and browse to it
fName = dSets.dataSet(markerHtml).cell("filename", "code").toString
Select Case eOutput
Case eOutputHtml
If openNewHtml(fName, generateHtml(job, dSets, markerHtml)) Then
pickABrowser dSets, fName, True
End If
Case eOutputKML
If openNewHtml(fName, generateKML(job, dSets)) Then
pickABrowser dSets, fName, True
End If
Case Else
Debug.Assert False
End Select
dSets.tearDown
Set dSets = Nothing
End Sub
Private Function generateHtml(job As cJobject, dSets As cDataSets, mHtml As String) As String
Dim s1 As String
' the deserialized data
s1 = "function mcpherDataPopulate() { " & vbCrLf & _
"var mcpherData = " & job.serialize(True) & ";" & vbCrLf & _
"return mcpherData; };"
With dSets.dataSet(mHtml)
generateHtml = _
.cell("header", "code").toString & vbCrLf _
& s1 & vbCrLf _
& .cell("main", "code").toString & vbCrLf _
& .cell("catfunctions", "code").toString & vbCrLf _
& .cell("functions", "code").toString & vbCrLf _
& .cell("body", "code").toString & vbCrLf
End With
End Function
Private Function generateKML(job As cJobject, dSets As cDataSets) As String
Dim s1 As String
Dim jo As cJobject
s1 = vbNullString
For Each jo In job.children
s1 = s1 & generatePlaceMark(jo)
Next jo
generateKML = _
"<?xml version='1.0' encoding='UTF-8'?>" & vbLf & _
"<kml xmlns='http://www.opengis.net/kml/2.2'>" & vbLf & _
tag("Document", s1) & "</kml>"
End Function
Private Function generatePlaceMark(job As cJobject) As String
' convert jSon item to KML
With job
generatePlaceMark = _
tag("Placemark", _
tag("name", .child("title").toString) & _
tag("description", "<![CDATA[" & .child("content").toString & "]]>") & _
tag("Point", _
tag("coordinates", .child("lng").toString & "," & .child("lat").toString) & ",0"))
End With
End Function
Private Function tag(tagName As String, Optional item As String = vbNullString) As String
tag = "<" & tagName & ">" & vbLf & item & vbLf & "</" & tagName & ">"
End Function
The Vba code
Most of the code used here is already covered in Creating Google Maps Layers from Excel Data, json, Data Manipulation Classes, and Google Visualization but here is the specific code for creating the html file from the Customer Worksheet.
' This will take the geocoded data and mark it
Option Explicit
'for more about this
' https://ramblings.mcpher.com/classes/datamanip/
'to contact me
' https://gitter.im/desktopliberation/community
' https://ramblings.mcpher.com/reusing-code-from-this-site/
Public Sub genericMarking(paramName As String, markerHtml As String, _
Optional eOutput As eOutputMarkers = eOutputHtml)
Dim dSets As cDataSets, dc As cCell, job As cJobject, fName As String
Dim dr As cDataRow, vc As cCell, a As Variant, i As Long
Set dSets = dSetsSetup(paramName)
If dSets Is Nothing Then Exit Sub
' check that we have the required marker fields
With dSets
For Each dc In .dataSet(cMarkers).column("Column Name").rows
If .dataSet(cMarkers).isCellTrue(dc.row, "required") Then
With .dataSet(cMaster).headingRow
If Not .validate(True, dc.toString) Then Exit Sub
End With
End If
Next dc
End With
' we have it all now create a job
Set job = New cJobject
With job.init(Nothing)
' add framework/control parameters
With .add("framework").add("control")
For Each dr In dSets.dataSet(cControl).rows
.add dr.value(cControl), dr.value(cControlValue)
Next dr
End With
With .add("cJobject").addArray
For Each dr In dSets.dataSet(cMaster).rows
With .add
For Each dc In dSets.dataSet(cMarkers).column(cMarkers).rows
If Not dSets.dataSet(cMarkers).isCellTrue(dc.row, "array") Then
Set vc = dr.cell(dc.parent.cell("Column Name").toString)
If Not vc Is Nothing Then
.add dc.toString, vc.toString
End If
Else
a = Split(dc.parent.cell("Column Name").toString, ",")
With .add(dc.toString).addArray
For i = LBound(a) To UBound(a)
Set vc = dr.cell(CStr(a(i)))
If Not vc Is Nothing Then
.add.add CStr(a(i)), vc.toString
End If
Next i
End With
End If
Next dc
End With
Next dr
End With
End With
' now create the html file and browse to it
fName = dSets.dataSet(markerHtml).cell("filename", "code").toString
Select Case eOutput
Case eOutputHtml
If openNewHtml(fName, generateHtml(job, dSets, markerHtml)) Then
pickABrowser dSets, fName, True
End If
Case eOutputKML
If openNewHtml(fName, generateKML(job, dSets)) Then
pickABrowser dSets, fName, True
End If
Case Else
Debug.Assert False
End Select
dSets.tearDown
Set dSets = Nothing
End Sub
Private Function generateHtml(job As cJobject, dSets As cDataSets, mHtml As String) As String
Dim s1 As String
' the deserialized data
s1 = "function mcpherDataPopulate() { " & vbCrLf & _
"var mcpherData = " & job.serialize(True) & ";" & vbCrLf & _
"return mcpherData; };"
With dSets.dataSet(mHtml)
generateHtml = _
.cell("header", "code").toString & vbCrLf _
& s1 & vbCrLf _
& .cell("main", "code").toString & vbCrLf _
& .cell("catfunctions", "code").toString & vbCrLf _
& .cell("functions", "code").toString & vbCrLf _
& .cell("body", "code").toString & vbCrLf
End With
End Function
Private Function generateKML(job As cJobject, dSets As cDataSets) As String
Dim s1 As String
Dim jo As cJobject
s1 = vbNullString
For Each jo In job.children
s1 = s1 & generatePlaceMark(jo)
Next jo
generateKML = _
"<!--?xml version='1.0' encoding='UTF-8'?-->" & vbLf & _
"" & vbLf & _
tag("Document", s1) & ""
End Function
Private Function generatePlaceMark(job As cJobject) As String
' convert jSon item to KML
With job
generatePlaceMark = _
tag("Placemark", _
tag("name", .child("title").toString) & _
tag("description", "<![CDATA[" & .child("content").toString & "]]>") & _
tag("Point", _
tag("coordinates", .child("lng").toString & "," & .child("lat").toString) & ",0"))
End With
End Function
Private Function tag(tagName As String, Optional item As String = vbNullString) As String
tag = "<" & tagName & ">" & vbLf & item & vbLf & "</" & tagName & ">"
End Function
Summary
Google Maps makes this kind of application – just a few years ago it would have been unheard of – just a matter of plugging a few bits and pieces together. The cDataSet class and the cJobject class together make the whole business of dealing with worksheets, setting up parameters, creating javaScript data friending and serializing to jSon very simple and open up your spreadsheets to all manner of new web service capabilities.
I look forward to your comments, questions and feedback on our forum. Please consider joining and participating our blog for the latest updates and useful Excel Ramblings discussions and code.