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 8 of 8

Excel to Mappoint Macro

This is a discussion on Excel to Mappoint Macro within the MapPoint Desktop Discussion forums, part of the Map Forums category; Hi All, first time poster , not sure if this is possibe. I am trying to automate the plotting of ...

  1. #1
    Paddy1979 is offline Junior Member White Belt
    Join Date
    Jun 2008
    Posts
    5

    Excel to Mappoint Macro

    Hi All, first time poster , not sure if this is possibe.

    I am trying to automate the plotting of territories from an excel file into Mappoint Europe 2004

    For example on the spreadsheet in the Activesheet i have:
    Range A2:A10 contains Postcode sectors
    Range B2:B10 contains the name of the salesman covering the area

    A1 & B1 are Column Headers, Postcode Sector & Salesman Name

    Is it possible to auto upload his data into territories in Mappoint ??

    I am fairly familiar with excel vba but am lost with this mappoint puzzle.

    Many Thanks

  2. #2
    Mattys Consulting's Avatar
    Mattys Consulting is offline Senior Member Black Belt
    Join Date
    Dec 2002
    Posts
    1,040

    Re: Excel to Mappoint Macro

    Make a function that can be called from both a button
    and the Open event of the WorkBook that updates the
    the territories using the specified ranges.
    Or wasn't that the question?

    Mike Mattys

  3. #3
    Paddy1979 is offline Junior Member White Belt
    Join Date
    Jun 2008
    Posts
    5

    Re: Excel to Mappoint Macro

    Hi Matty,

    Sorry to be dumb but....

    I need an Excel macro that of a cmd button click will:

    1. Open Mappoint ( this bit i have picked up )

    2. Auto Import a Territory source file from a speadsheet named PostS.xls from the directotry C:\Documents and Settings\pkelly\Desktop

    3. Data will be set as Postcode Sectors in Col A and Peoples Names in Col B

    Not sure how to create a function that could do the above , is it possible ??

    Thanks

  4. #4
    Mattys Consulting's Avatar
    Mattys Consulting is offline Senior Member Black Belt
    Join Date
    Dec 2002
    Posts
    1,040

    Re: Excel to Mappoint Macro

    Hi,

    You're just not making yourself familiar with the
    best resource, aside from this list, available to you.

    From the ImportTerritories method in MapPoint HELP:

    Sub CreateTerritoriesMap()
    Dim objApp As New MapPoint.Application
    objApp.Visible = True
    objApp.UserControl = True
    Dim szconn As String
    Dim oDS As MapPoint.DataSet
    With objApp.ActiveMap.DataSets
    'Excel sheet
    szconn = objApp.Path & "\Samples\Terrs.xls!Sheet1!A1:E127"
    Set oDS = .ImportTerritories(szconn, , _
    geoCountryUnitedStates, , geoImportExcelA1)
    End With
    End Sub

    Mike Mattys

  5. #5
    Paddy1979 is offline Junior Member White Belt
    Join Date
    Jun 2008
    Posts
    5

    Re: Excel to Mappoint Macro

    Thanks Matty, that works great

    Just 2 things

    1.When it uploads the territorties the view does not auto zoom as if imported manually, can the zoom be automated ?

    2. Can i display the Postcode Sectors within the imported Territoires ?

    Thanks again.

    Sorry if these are basic q's but i only had Mappoint uploaded onto my Laptop 2 days ago , still trying to get to grips with it.

  6. #6
    Paddy1979 is offline Junior Member White Belt
    Join Date
    Jun 2008
    Posts
    5

    Re: Excel to Mappoint Macro

    Got there in the end, thanks for the help

    Code:
    Sub CreateTerritoriesMap()
    Dim objApp As New MapPoint.Application
    Dim objMap As MapPoint.Map
    Dim objLoc As MapPoint.Location
    Dim szconn As String
    Dim oDS As MapPoint.DataSet
    Set objMap = objApp.ActiveMap
    ' start mappoint
    objApp.Visible = True
    objApp.UserControl = True
    ' open excel file and import data set
    With objApp.ActiveMap.DataSets
       szconn = "\Documents and Settings\pholly\Desktop\Test.xls!Sheet1!A1:B229"
       Set oDS = .ImportTerritories(szconn, , geoCountryeurope, , geoImportExcelA1)
    End With
    ' zoom in on the fist postcode in the worksheet
    Set objLoc = objMap.FindResults("B21").Item(1)
    objLoc.GoTo
    ' zoom out slightly
    For i = 1 To 5
      objApp.ActiveMap.ZoomOut
    Next i
    
    End Sub

  7. #7
    Mattys Consulting's Avatar
    Mattys Consulting is offline Senior Member Black Belt
    Join Date
    Dec 2002
    Posts
    1,040

    Re: Excel to Mappoint Macro

    HI Paddy1979,

    Good. For ... Next is a great way to do it.
    However, setting the altitude of the map would be better

    Now look through all the all the Objects, Properties, and Methods
    of the Programmer's Reference in Help. Know the object model.
    You will be an expert in no time.

    Mike Mattys

  8. #8
    Paddy1979 is offline Junior Member White Belt
    Join Date
    Jun 2008
    Posts
    5

    Re: Excel to Mappoint Macro

    yep this is better

    objApp.ActiveMap.Altitude = 70

    Cheers Matty

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Using a macro to get a map into excel
    By jimbob in forum MapPoint Desktop Discussion
    Replies: 3
    Last Post: 03-19-2009, 04:21 PM
  2. Macro to Export to Mappoint (from Excel)
    By infinityukok in forum MapPoint Desktop Discussion
    Replies: 3
    Last Post: 08-03-2007, 06:39 AM
  3. Excel macro based on Highlighted Mappoint pushpins
    By RHill in forum MapPoint Desktop Discussion
    Replies: 2
    Last Post: 10-11-2006, 08:18 AM
  4. Replies: 0
    Last Post: 04-01-2005, 09:31 AM
  5. updatelink using macro in Excel XP
    By amyles in forum MapPoint Desktop Discussion
    Replies: 0
    Last Post: 07-17-2003, 01:26 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 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127