I have a project where I have an Excel workbook with several sheets. Currently each sheet has a map embeded in it. I need to be able to map data for each sheet on the correct map using VBA.

Method 1) When I try to loop through the sheets currently and link to the named range holding the address it places all of the pushpins on the map embeded on the first worksheet. I can not find a collection of maps to reference so that I can be sure I am getting the correct one.

Method 2) My other thought, which I would prefer due to the resources used by the above example, is to have one embeded map and just move it to the active sheet when you click on the tab for that sheet. Once the map is moved to the active sheet I can then link to the correct data on that sheet. The problem here is I can not get the embeded map to move from one sheet to another using VBA.

Code for method 1:

Code:

Public Sub MapIt(ws As worksheet, rcsutid as string)
Dim oMap As MapPoint.Map
Dim objPin As MapPoint.Pushpin
Dim szconn As String
Dim oDS As MapPoint.DataSet
Dim obj As OLEObject
Dim lRangeValues(1 To 3) As Variant
Dim zRangeNames(1 To 3) As String
Dim RS As MapPoint.Recordset
Dim High As Integer
Dim oDM As MapPoint.DataMap
 
On Error GoTo ErrorHandler
'make map interactive
ws.OLEObjects(1).Activate
'Get map
Set oMap = GetObject(, "MapPoint.Application").ActiveMap
'Clean out old datasets
For Each oDS In oMap.DataSets
oDS.Delete
Next
'Create new datasets
With oMap.DataSets
szconn = ActiveWorkbook.Path & "\JI_Dealer_Business_Plan.xls!" & ws.Name & "!location"
Set oDS = .ImportData(szconn, , geoCountryUnitedStates, , geoImportExcelNamedRange)
oDS.DisplayPushpinMap
Set RS = oDS.QueryAllRecords
RS.MoveFirst
Set objPin = RS.Pushpin
objPin.Location.Goto
oMap.ZoomIn
objPin.Symbol = 291
oDS.Name = rcustid & "Dealer Location"
szconn = ActiveWorkbook.Path & "\JI_Dealer_Business_Plan.xls!" & ws.Name & "!counties"
Set oDS = .ImportData(szconn, , geoCountryUnitedStates, , geoImportExcelNamedRange)
Set RS = oDS.QueryAllRecords
RS.MoveFirst
High = 0
Do Until RS.EOF
If High < CInt(RS.Fields("I | K | D").Value) Then
High = CInt(RS.Fields("I | K | D").Value)
End If
RS.MoveNext
Loop
lRangeValues(1) = 0
lRangeValues(3) = High
zRangeNames(1) = "Low 0"
zRangeNames(2) = "Medium " & High / 2
zRangeNames(3) = "High " & High
Set oDM = oDS.DisplayDataMap( _
geoDataMapTypeShadedArea, _
, , , geoRangeTypeContinuous, geoRangeOrderHighToLow, 13, _
, lRangeValues, zRangeNames)
oDM.LegendTitle = "Sales By County for " & rcustid
oDS.Name = "Sales By County"
End With
'Change map to Data Map
oMap.MapStyle = geoMapStyleData
'Inactivate map
ws.Range("a1").Select
GoTo Done
ErrorHandler:
MsgBox Err.Description
Done:
End Sub

Thank you,
Brian Wagner

Hi,

Please put next time code into [ code ] [ / code ] tags (without the spaces) for readability.

Method 1: There is no collection of maps, so you have to keep the household of it yourself. A collection is basically just a linked list. But this method is as you self have sayed very memory consuming if you have lots of sheets with maps open.

Method2: Looks nice, but I dont think you can move a map from control to other control. But what you sure can do, is move the whole control from the inactive sheet to the active sheet.

Method3: Just in case 1 and 2 dont work you can have a mp control on all sheet, and open a map on active sheet and always close all other maps. But this will elapse more time.

I have scrapped Method 1, and am trying Method 2. I have been able to delete the existing map on DactivateSheet and add map on Activate sheet, but I have not been able to move the control. Is there sample code somewhere on moving the MapPoint Control from one sheet to another?

Tnks,
Brian Wagner

Hi,

Never tryed it but should be possible. I have suspicion that if you have the control on a sheet, it is exacly the same as having the control on a form, right ?

So dont add it on design time, but on runtime. To add:

Code:

// first create it etc...
// Name of the control here is MP
// Owner is the control where the control is sitting on, eg the sheet

MP.BeginInit();
Owner.Controls.Add(MP);
MP.EndInit();
MP.Visible = true;

// I'm not sure on the sequence for the BeginInit :(
// To move it:
// Owner 2 is the second form / sheet

MP.Visible = false;
Owner.Controls.Remove(MP);
Owner2.Controls.Add(MP);
MP.Visible = true;

// Again I'm not sure what to do with begin/end Init()

This is not test and from the top of my head, so please bear if it crash your system In VBA syntax a little different but this should let you go I think.

Please feedback results, it can benefit other people a lot.