Welcome to MapForums!

Register, sign in, or use Facebook Connect above to join in and participate in the forum.

When you are signed in, this message, the ads in this row, and the red-underlined link ads all go away.

Subscribe to receive our newsletter.
Subscribe Unsubscribe
Results 1 to 4 of 4

Manipulating multiple maps in Excel

This is a discussion on Manipulating multiple maps in Excel within the MapPoint Desktop Discussion forums, part of the Map Forums category; I have a project where I have an Excel workbook with several sheets. Currently each sheet has a map embeded ...

  1. #1
    briwagner is offline Junior Member White Belt
    Join Date
    Sep 2006
    Posts
    2

    Manipulating multiple maps in Excel

    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
    Last edited by Eric Frost; 09-26-2006 at 12:18 PM. Reason: added code tags (but not indentation)

  2. #2
    Wilfried is offline Senior Member Black Belt
    Join Date
    Nov 2004
    Location
    Belgium
    Posts
    2,433
    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.

  3. #3
    briwagner is offline Junior Member White Belt
    Join Date
    Sep 2006
    Posts
    2
    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

  4. #4
    Wilfried is offline Senior Member Black Belt
    Join Date
    Nov 2004
    Location
    Belgium
    Posts
    2,433
    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.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. New user: Exporting multiple data sets to excel
    By rpone605 in forum Products: Pushpin Tool, Single State Mapper
    Replies: 0
    Last Post: 06-20-2006, 04:54 PM
  2. Updating Maps in Excel without Mappoint
    By Cuda in forum MapPoint Desktop Discussion
    Replies: 5
    Last Post: 02-06-2006, 10:16 PM
  3. Importing multiple maps into Excel
    By dadburne in forum MapPoint Desktop Discussion
    Replies: 5
    Last Post: 10-19-2005, 01:07 PM
  4. Error with Multiple Symbol Maps
    By Anonymous in forum MapPoint Desktop Discussion
    Replies: 1
    Last Post: 01-13-2005, 04:31 PM
  5. MPControl embedded in excel - excel loses functionality
    By ruyasan in forum MapPoint Desktop Discussion
    Replies: 2
    Last Post: 05-20-2004, 04:41 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25