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

MapPoint 2009 - Importing data from Excel

This is a discussion on MapPoint 2009 - Importing data from Excel within the MapPoint Desktop Discussion forums, part of the Map Forums category; Hello everyone, I have only recently started using MapPoint so my knowledge of this software is literally non-existant. My VBA ...

  1. #1
    APP
    APP is offline Junior Member White Belt
    Join Date
    May 2012
    Posts
    8

    MapPoint 2009 - Importing data from Excel

    Hello everyone,

    I have only recently started using MapPoint so my knowledge of this software is literally non-existant. My VBA knowldge, as you will notice, is also quite limited.

    What I am trying to do is to simply enter data in Excel (country, city and post code) and then create a macro which will open MapPoint and enter the postcodes in RoutePlanner. From previous threads in this forum I found a VBA code which does exactly what i want but when I tried to use it, nothing is entered in RoutePlanner.

    Code:
    Sub Add_Address_To_Route_Planner()
    '
    ' Add_Address_To_Route_Planner Macro
    
    
    Set App = CreateObject("MapPoint.Application")
        App.Visible = True
        Set objMap = App.NewMap
        Set objRoute = objMap.ActiveRoute
    
        Row = 2
        While Cells(Row, 1) < "5"
        objRoute.Waypoints.Add objMap.FindAddressResults([Country], [City], [PostalCode])
        Cells(Row, 1) = [Country]
        Cells(Row, 2) = [City]
        Cells(Row, 3) = [PostalCode]
            
        Row = Row + 1
        Wend
    
    MsgBox ("Click ok to retun to excel")
    
    '
    End Sub
    Can someone plz have a look at the code and let me know what i am doing wrong?

    Btw I am currently using Excel 2007 and MapPoint 2009.

    Thank you very much

  2. #2
    Eric Frost's Avatar
    Eric Frost is offline Administrator Black Belt
    Join Date
    Jul 1992
    Posts
    5,088
    Blog Entries
    4

    Re: MapPoint 2009 - Importing data from Excel

    Can you post a sample spreadsheet with some data? (zip and attached to your post)

    The row/col's referenced in the code need to match the worksheet, etc.

    Eric
    ~ Order MapPoint MapPoint 2013 Here and Get Free Access to the MapForums Downloads ~
    ~~
    ~ Upgrade to Get Access to the MapForums Downloads ~

  3. #3
    APP
    APP is offline Junior Member White Belt
    Join Date
    May 2012
    Posts
    8

    Re: MapPoint 2009 - Importing data from Excel

    Thanks a lot for the prompt reply Eric.

    The spreadsheet is attached on my reply.
    Attached Files Attached Files

  4. #4
    APP
    APP is offline Junior Member White Belt
    Join Date
    May 2012
    Posts
    8

    Re: MapPoint 2009 - Importing data from Excel

    Problem has been solved.

    Thx for the help Eric

  5. #5
    Eric Frost's Avatar
    Eric Frost is offline Administrator Black Belt
    Join Date
    Jul 1992
    Posts
    5,088
    Blog Entries
    4

    Re: MapPoint 2009 - Importing data from Excel

    What does your code look like now? I made several changes.

    First, I'd recommend to go to Tool | References and add a reference to MapPoint.

    Also, Declare (Dim) the app and map objects (see below). This makes it much easier to work with the code; you get context-sensitive help.


    Code:
    Sub Add_Address_To_Route_Planner()
    
        Dim App As MapPoint.Application
        Dim objMap As MapPoint.Map
        
        Set App = CreateObject("MapPoint.Application.EU")
        App.Visible = True
        Set objMap = App.NewMap
        Set objRoute = objMap.ActiveRoute
    
        Row = 2
        While Row < 5
        objRoute.Waypoints.Add objMap.FindAddressResults(, Cells(Row, 2), , , Cells(Row, 3), Cells(Row, 1))(1)
        Row = Row + 1
        Wend
    
    End Sub
    ~ Order MapPoint MapPoint 2013 Here and Get Free Access to the MapForums Downloads ~
    ~~
    ~ Upgrade to Get Access to the MapForums Downloads ~

  6. #6
    APP
    APP is offline Junior Member White Belt
    Join Date
    May 2012
    Posts
    8

    Re: MapPoint 2009 - Importing data from Excel

    thx for providing the code Eric. i also have to say that you have done an amazing job in providing so many tutorials on how to operate mappoint from excel. they were really helpful.

    the code that i have used is

    Code:
    Dim mp As MapPoint.Application
    Sub Macro1()
    '
    ' Macro1 Macro
    '
    Set mp = CreateObject("mappoint.application.eu.16")
    mp.Visible = True
    Set objMap = mp.newmap
    Set objroute = objMap.activeroute
    
    Row = 2
    
    While Cells(Row, 5) <> ""
    objroute.Waypoints.Add objMap.FindAddressResults( _
    Cells(Row, 1), _
    Cells(Row, 2), _
    Cells(Row, 3), _
    Cells(Row, 4), _
    Cells(Row, 5)).Item(1)
    
    Row = Row + 1
    
    Wend
    
    End Sub
    i have two more questions though and i wondering if you can help me:

    1) do you know what algorithm does MapPoint use to solve TSP problems? I have used the "Optimise Stops" options and i was impressed with the results even though it iseems to be quite slow.

    2) is there any way of constructing a distance matrix in Excel by using the data provided from MapPoint? Ideally i would like to do that without the need to install any more add-ins

  7. #7
    Eric Frost's Avatar
    Eric Frost is offline Administrator Black Belt
    Join Date
    Jul 1992
    Posts
    5,088
    Blog Entries
    4

    Re: MapPoint 2009 - Importing data from Excel

    #1, no I do not sorry...

    #2, for a distance matrix, yes you could do this with some Excel VBA code, but it would be easier to use something off the shelf. Have you explored some tools thus far? I'm not sure which would be more appropriate - MPMileage or MPMileCharter?

    Eric
    Last edited by Eric Frost; 06-04-2012 at 08:18 AM.
    ~ Order MapPoint MapPoint 2013 Here and Get Free Access to the MapForums Downloads ~
    ~~
    ~ Upgrade to Get Access to the MapForums Downloads ~

  8. #8
    Winwaed's Avatar
    Winwaed is offline Mapping-Tools.com Black Belt
    Join Date
    Feb 2004
    Location
    Irving,Texas
    Posts
    1,827
    Blog Entries
    55

    Re: MapPoint 2009 - Importing data from Excel

    For a distance matrix, you mean between two groups of pins? The Basic License of MPMileCharter would do this ( MPMileCharter: Home Page ). Note you are going to need to either do it by hand or with API coding - whether your own or purchased.Purchasing saves you the time it takes to fix problems in your code!

    Note that the above code assumes FindAddressResults returns a valid location - and in the case of multiple locations, the first one is the correct one (this is rarely the case). You should check the ResultsQuality property of the object returned by FindAddressResults, before blindly inserting the first entry (which might not even exist!) into your route.
    Winwaed Software Technology LLC
    http://www.winwaed.com
    See http://www.mapping-tools.com for MapPoint Tools

  9. #9
    APP
    APP is offline Junior Member White Belt
    Join Date
    May 2012
    Posts
    8

    Re: MapPoint 2009 - Importing data from Excel

    Quote Originally Posted by Eric Frost View Post

    #2, for a distance matrix, yes you could do this with some Excel VBA code, but it would be easier to use something off the shelf. Have you explored some tools thus far? I'm not sure which would be more appropriate - MPMileage or MPMileCharter?

    Eric
    I would prefer to write my own VBA code since I am not sure if I can purchase a dedicated tool for calculating distance matrices. If things turn ugly on the way though I will consider it more seriously

  10. #10
    APP
    APP is offline Junior Member White Belt
    Join Date
    May 2012
    Posts
    8

    Re: MapPoint 2009 - Importing data from Excel

    Quote Originally Posted by Winwaed View Post
    For a distance matrix, you mean between two groups of pins? The Basic License of MPMileCharter would do this ( MPMileCharter: Home Page ). Note you are going to need to either do it by hand or with API coding - whether your own or purchased.Purchasing saves you the time it takes to fix problems in your code!

    Note that the above code assumes FindAddressResults returns a valid location - and in the case of multiple locations, the first one is the correct one (this is rarely the case). You should check the ResultsQuality property of the object returned by FindAddressResults, before blindly inserting the first entry (which might not even exist!) into your route.
    When I say distance matrix I mean derive a matrix which will calculate the distances between all of the postcodes to be used in the model (yes it will most probably be huge). This is because I will need such a matrix for further analysis.

    With regards to the FindAddressResults returning an invalid location, that was something that I also noticed and couldn't understand why it was happening since the postcodes I was entering appeared to be correct. I eventually learned to check the results produced every time just in case MapPoint starts acting all weird again. Thank you for the tip though.

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Importing Excel Data Issue (my version)
    By Egwin32 in forum MapPoint Desktop Discussion
    Replies: 5
    Last Post: 08-16-2010, 09:49 AM
  2. Importing Excel Data Issue
    By Junior-Jo3X in forum MapPoint Desktop Discussion
    Replies: 3
    Last Post: 08-15-2010, 04:48 PM
  3. VERY Novice Question About Importing Excel Data to Map Locations
    By hoosierquilt in forum MapPoint Desktop Discussion
    Replies: 5
    Last Post: 05-22-2009, 10:29 AM
  4. Importing into MapPoint 2009
    By KwoldOrkin in forum MapPoint Desktop Discussion
    Replies: 1
    Last Post: 12-09-2008, 06:17 AM
  5. importing data from excel to mappoint
    By Layne in forum MapPoint Desktop Discussion
    Replies: 0
    Last Post: 05-09-2003, 12:40 PM

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