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
Page 1 of 2 12 LastLast
Results 1 to 10 of 19

Import Addresses from Excel using VBA

This is a discussion on Import Addresses from Excel using VBA within the MapPoint Desktop Discussion forums, part of the Map Forums category; Hi everyone, Quick question and I will apologize in advance if it has been posted before but I was not ...

  1. #1
    Sidereus is offline Junior Member White Belt
    Join Date
    Nov 2008
    Posts
    5

    Import Addresses from Excel using VBA

    Hi everyone,

    Quick question and I will apologize in advance if it has been posted before but I was not able to find it in the forums using a search.

    My problem relates to getting my addresses into the Mappoint route planner in the same order that they are in my excel file.

    Now from the reading I have done, I do understand that this is not a Mappoint feature and I also understand why Mappoint loads them in a different order.

    So where was my thought.

    If you open the route planner and add addresses 1 and a time, they automatically get listed in the order they are entered.

    Based on this, I simply want to write a simple VBA macro to run from excel that launches Mappoint and basically adds my address information line by line as it appears in excel.

    No need to format the excel file or be worried about checking the sequence as the file is generated as formatted in chronological order already.

    Also, there will not be any blanks between rows, and a blank cell will indicate the end of the data.

    Basic idea I have is that this import function will need to be done in a loop until it gets to a row with no data at which point it ends.

    While I have a basic programming knowledge, I am not familiar wit the VBA code / function names from Mappoint to get this to work.

    Any help would be appreciated.

    (And no, I am not interested in purchasing Routewriter as I feel this should not be a complicated task for the simple use I need it for)

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

    Re: Import Addresses from Excel using VBA

    If you don't want to do the programming yourself and looking to hire someone it's probably going to cost you more than RouteWriter I think those tools are pretty inexpensive for what they do.

    There are some MapPoint / Excel programming examples at Working With Excel and MapPoint and also in the newsletter tutorials MP2K Update Archives

    Also, full source code (mostly Excel VBA) is included with the tools in the MapForums Downloads section ($99). MapForums Plus - MapForums Plus Downloads at MapForums.com

    There are a few that come to mind that would probably be the closest to doing what you want --

    Import a Route with Stoptimes
    Import a Route with Stoptimes and Depart Times
    MapPoint Power Tools Spreadsheet

    That said, if you post the code you have so far and let us know where you are encountering difficulties, we can help you right here as well!

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

  3. #3
    Sidereus is offline Junior Member White Belt
    Join Date
    Nov 2008
    Posts
    5

    Re: Import Addresses from Excel using VBA

    Simple & Free.

    Code:
    Sub AddresstoMapPoint()
    
    Set App = CreateObject("MapPoint.Application.NA.16") 
        App.Visible = True 
        Set objMap = App.NewMap 
        Set objRoute = objMap.ActiveRoute 
    
        Row = 2 
        While Cells(Row, 1) <> "" 
        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 
    
    MsgBox ("Click ok to retun to excel")
    
    End Sub
    Really simple macro that takes your excel file then adds the information line by line to the route planner, giving you your route as it showed on your excel spreadsheet.

    The while loop is one big argument, where each "Cells..." populates a different part of the requirement for the find address function.

    FindAddress Syntax:

    object.FindAddressResults([Street], [City], [OtherCity], [Region], [PostalCode], [Country])

    Quick & Dirty to use it:

    Change the numbers of the above while loop (integers only i.e. 1, 2, 3, etc) to match your column where the correct data is stored.

    Mine works with 1 through 5 since I have a different macro that will automatically format and rearrange my canned report to place the needed information in columns 1 through 5.

    Really simple and works as I asked above. Too hard to find the right ideas or maybe I wasn't asking the right questions. Either way, based on how many people look for this type of idea, hopefully it helps someone else in the future.
    Last edited by Eric Frost; 09-28-2011 at 01:13 PM.

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

    Re: Import Addresses from Excel using VBA

    Sweet! Very good work. You are probably finding MapPoint is easier to program than you thought. The Help file is really good with tons of examples.

    A couple comments:
    • For this line, you can omit the .NA and .16.

      Code:
      Set App = CreateObject("MapPoint.Application.NA.16")
      Then it will work with whatever version of MapPoint is installed on your computer, whether it is European or North American or year (2006/2009/2010/2011, 2012?).

    • Also, if you have lat/lon instead of address, you would modify the Waypoint.Add line to be something like:

      Code:
      objRoute.Waypoints.Add objMap.GetLocation(Cells(Row, 1), Cells(Row, 2))


    Anyway, glad you figured it out so easily and thanks for posting your solution!

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

  5. #5
    Sidereus is offline Junior Member White Belt
    Join Date
    Nov 2008
    Posts
    5

    Re: Import Addresses from Excel using VBA

    Last little issue with this.

    I can not for the life of me get MP to zoom to the waypoints (automatically) once they are all placed, I am sure that I must be missing something simple as I have tried all types of variances with .GoTo and .ZoomTo and nothing.

    Anyone who can help on this, it would be appreciated.

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

    ZoomTo Route

    The route object itself has a ZoomTo method, see attached.

    Did you try this or were you trying the .ZoomTo method on the WayPoints?

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

  7. #7
    Sidereus is offline Junior Member White Belt
    Join Date
    Nov 2008
    Posts
    5

    Re: Import Addresses from Excel using VBA

    No sure what version you are using, but with mine under methods for a route object it only shows Calculate and clear

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

    Re: Import Addresses from Excel using VBA

    Oops, what version are you using? It's possible they added that method. There was a ton of new stuff in 2009 and 2010, especially 2010. Current version is 2011.

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

  9. #9
    Sidereus is offline Junior Member White Belt
    Join Date
    Nov 2008
    Posts
    5

    Re: Import Addresses from Excel using VBA

    I am running 2009.

    I actually thought that I should be able to use that type of method and tried all kinds of ways to apply it to the ojbRoute in my code above with 0 success.

    The most direct way it seems to do it is with the .DataSets.ZoomTo however only pushpin data seems to be a dependent of DataSets and not waypoints.

    The only way I can get it to zoom on the route is the use the .Calculate function but then it calculates the route and shows it on the screen which I do not want at that point as before this can be done, I need to enter different start / end points.

    Very Frustrating. I would at this point settle for the .Calculate method if I could somehow get MP to not show the route directions to the screen, but even this is not idea as it wastes unnecessary computing time.

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

    Zooming to Waypoints

    Is upgrading to get the new Zoomto method an option?

    Also, is just zooming in manually that bad? You're not automating anything after that which is dependent on the Zoomto, correct?

    Besides calculate, you could code something to add pushpins at all those location which would create a dataset, and then zoom to that. Then if you don't want those pushpins on the map, just delete the dataset. To make it slightly faster, just add four pushpins at the corners using some min/max code

    Code:
               maxX = Application.Max(maxX, oRS.Location.Longitude)
               minX = Application.Min(minX, oRS.Location.Longitude)
               maxY = Application.Max(maxY, oRS.Location.Latitude)
               minY = Application.Min(minY, oRS.Location.Latitude)
    hope this helps!

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

Page 1 of 2 12 LastLast

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Cannot Import Excel Files
    By GKing in forum MapPoint Desktop Discussion
    Replies: 7
    Last Post: 3 Weeks Ago, 07:17 AM
  2. order of the addresses in the excel export
    By pileggi in forum MapPoint Desktop Discussion
    Replies: 2
    Last Post: 01-27-2010, 09:48 AM
  3. Import a list of addresses from Excel to create optimized route
    By bran987 in forum MapPoint Desktop Discussion
    Replies: 1
    Last Post: 10-10-2009, 08:33 PM
  4. Import on Excel
    By Anonymous in forum MapPoint Desktop Discussion
    Replies: 0
    Last Post: 12-01-2003, 12:48 PM
  5. Let's try again. I want to import addresses into ....
    By Anonymous in forum MapPoint Desktop Discussion
    Replies: 1
    Last Post: 08-10-2001, 02:45 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