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)

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

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.

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

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.

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

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

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

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.

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