I'm building an Excel model which has a VBA module which needs to read in a set of locations (lat/long data) from Excel and find an optimal route around the points - as if you were using the "optimize stops" button in MapPoint.

Could anyone help me with the code?

I know how to add in individual pushpins using VBA code but not a large set of points (say 150) to add to the route.

Version is MP2002 Europe

okay, I've sussed out how to read the points in

now I just need to optimise the stops

my current line of code is: (oRoute is my MapPoint.Route object)

oRoute.Calculate

but I need something which will not just find the route as they were inputted but the optimal order.

Maybe it's a little non-intuitive, but the Optimize method is a method of the WayPoints collection, not the Route like you might think.

Check out the help file

WayPoints is a property of the Route object.

WayPoints itself is a Collection.

WayPoints has the Optimize method and an IsOptimized property.

Here is the description and code sample for the Optimize method from the Help file (actually copied from the MapPoint 2004 help file so it might be a little different from yours).

Optimize method

Reorders the intermediate stops on a route so that the travel time between the start and end points is the most efficient; similar to clicking Optimize Stops on the Route menu.

Applies to
Collections: Waypoints

Syntax
object.Optimize

Part Description
object Required. An expression that returns a Waypoints collection.

Remarks

The RouteAfterOptimize event of the Map or MappointControl object occurs after the stops on the route have been optimized.

After stops are optimized, the route no longer exists on the map. Be sure to use the Calculate method on the Route object.

For more information about optimizing the stops on a route, see the Optimize the stops on your route procedural topic.

Example

Code:

 
  Sub OptimizeRoute()
  Dim objApp As New MapPoint.Application
  Dim objMap As MapPoint.Map
  Dim objRoute As MapPoint.Route
  'Set up the application
  Set objMap = objApp.ActiveMap
  Set objRoute = objMap.ActiveRoute
  objApp.Visible = True
  objApp.UserControl = True
  'Add route stops and calculate route
  With objRoute.Waypoints
    .Add objMap.FindResults("Seattle, WA").Item(1)
    .Add objMap.FindResults("Redmond, WA").Item(1)
    .Add objMap.FindResults("Tacoma, WA").Item(1)
    .Add objMap.FindResults("Bellevue, WA").Item(1)
  End With
  objRoute.Calculate
  'Optimize the route
  objRoute.Waypoints.Optimize
  End

SubNote This sample code is specific for use in MapPoint North America; it is for illustration purposes only.

thanks for that Eric. I'll give it a go

I actually tried

oRoute.Waypoints.Optimize = true

but it spat that out

It's a method with no parameters, I don't think you need to say anything, just do

.Optimize

not

.Optimize = Tue

best,
Eric