Trebor8484
Board Regular
- Joined
- Oct 27, 2018
- Messages
- 69
- Office Version
- 2013
- Platform
- Windows
Hi,
I have the code below that takes lat/longs from a file and plots them on a Google Map. The code generates a html file which automatically fires up in the web browser.
I have a couple of issues with this where I need to make amendments to the vba code and the underlying html.
Firstly I need to add a permanent label marker on each pin to show the time stamp from column C. Then when I click on a pin I need to see data relating to that marker including the information shown in column D plus possibly additional columns of data as well, however the code below seems to suggest that I can only have a 3 columns of data for this to work?
For the label marker, this works as a hard coded solution but I need to incorporate this or something similar into my macro
var marker2= new google.maps.Marker(
{
position: new google.maps.LatLng(53.593349, -2.296605),
label: {
color: 'black',
fontWeight: 'bold',
text: '14:20',
},
Full Code:
Sample data from my file:
Is anyone able to assist please?
Thanks
I have the code below that takes lat/longs from a file and plots them on a Google Map. The code generates a html file which automatically fires up in the web browser.
I have a couple of issues with this where I need to make amendments to the vba code and the underlying html.
Firstly I need to add a permanent label marker on each pin to show the time stamp from column C. Then when I click on a pin I need to see data relating to that marker including the information shown in column D plus possibly additional columns of data as well, however the code below seems to suggest that I can only have a 3 columns of data for this to work?
For the label marker, this works as a hard coded solution but I need to incorporate this or something similar into my macro
var marker2= new google.maps.Marker(
{
position: new google.maps.LatLng(53.593349, -2.296605),
label: {
color: 'black',
fontWeight: 'bold',
text: '14:20',
},
Full Code:
VBA Code:
Sub GenerateMap()
Dim c As Range
Dim FileName As String
Dim Label As String
Dim Latitude As String
Dim Longitude As String
Dim rng As Range
Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets("Mapping Data")
Set rng = ws.Range("A2:C" & ws.Cells(Rows.Count, "A").End(xlUp).Row)
If rng.Columns.Count < 2 Then
MsgBox "You need to have at least 2 columns;(1) the latitude and (2) the longitude.", vbCritical + vbOKOnly
Exit Sub
End If
If rng.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 = Environ("Temp") & "\Google Maps.html"
Latitude = Trim(rng.Cells(1, 1).Text)
Longitude = Trim(rng.Cells(1, 2).Text)
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: 10,"
Print #1, " center: new google.maps.LatLng(" + Latitude + ", " + Longitude + ")"
Print #1, " };"
Print #1, ""
Print #1, " var map = new google.maps.Map(document.getElementById('map-canvas'), mapOptions);"
For Each c In ws.Range("A2:A" & ws.Cells(Rows.Count, "A").End(xlUp).Row)
Latitude = Trim(Cells(c.Row, "A").Text)
If IsNumeric(Latitude) = False Then
MsgBox "The latitude in cell " & Cells(c.Row, "A").Address & " needs to be numeric.", vbCritical + vbOKOnly
Close #1
Exit Sub
End If
Longitude = Trim(Cells(c.Row, "B").Text)
If IsNumeric(Longitude) = False Then
MsgBox "The longitude in cell " & Cells(c.Row, "B").Address & " needs to be numeric.", vbCritical + vbOKOnly
Close #1
Exit Sub
End If
If rng.Columns.Count = 3 Then
Label = Trim(Cells(c.Row, "C").Text)
Else
Label = "Marker " + CStr(c.Row)
End If
Print #1, ""
Print #1, " var marker" + CStr(c.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: false,"
Print #1, " map: map"
Print #1, " });"
Print #1, ""
Print #1, " google.maps.event.addListener(marker" + CStr(c.Row) + _
", 'dragend', function(event)"
Print #1, " {"
Print #1, " var Title = marker" + CStr(c.Row) + ".getTitle();"
Print #1, " var SubStrings = Title.split(" + Chr$(34) + "\n" + Chr$(34) + ");"
Print #1, " marker" + CStr(c.Row) + ".setTitle(SubStrings[0] + " + _
Chr$(34) + "\n" + Chr$(34) + " + "; Chr$(34) + _
"The latitude and longitude at this location is: " + Chr$(34) + " + marker" + _
CStr(c.Row) + ".getPosition().toString());"
Print #1, " });"
Next c
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
Sample data from my file:
Latitude | Longitude | Time Stamp | Status | Comments |
53.463058 | -2.29134 | 14:00 | VCA | |
53.468979 | -2.37237 | 14:10 | GDR | ABC123 |
53.593349 | -2.296605 | 14:20 | GDR | |
53.593349 | -2.342708 | 14:30 | VCA | 123ABC |
Is anyone able to assist please?
Thanks