Is there anyway to reverse a route and maintain the specific stop times? I have code that will reverse the route and put the same stop time on every stop, but what I need to do is maintain the original, unique stop times.

Thanks,

Jim

Please correct me if I'm wrong here:
Do you mean that you'll start at 9:00, go to stop 1 at 9:10 then on to stop 2 at 9:20 and return to the same origin at 9:30?
This route is presumably optimized. After all, it is a database of road segments with associated driving times.
If your stop 2 is further away, it will be difficult to achieve the same stop times as you may not get to stop 2 til 9:15, stop 1 at 9:25 and return at 9:35.
It depends upon conditions such as road speeds, man-made and natural obstacles, traffic, left turns vs right turns / stop lights, time of day and traffic, etc.

Sorry, I worded the question poorly. I should have said "stop duration's".

What happens is before the route is imported to mappoint, my worksheet puts the stop duration time into each waypoint (10 mins generally). It looks to see if there are more than one delivery to that stop, and if there is only puts a stop time on one of those stops. Otherwise, if you have 3 deliveries at one stop it would make the stop time 30 mins and skew the rest of the waypoint arrival times.

It works fine when you optimize as usual, but I have another bit of code that optimizes the route, then reverses it and adds the stop duration times back in. The problem I have is I cannot find a way to maintain the original stoptime that the worksheet calculated uniquely for each stop.

I probably just made this more confusing

Thanks,
Jim

Well, that didn't really clarify the problem for me and I'll likely need to see for myself.
Your last statement is that if there is more than one delivery at a stop, use only one stop time.
So add 10 minutes for each delivery; 2 deliveries = 20 minutes, 3 = 30 minutes.
Optimizing forward uses the stop times (duration) correctly, but the arrival times (which, apparently, are already set?) then receive late warnings.
I have to say that it does not work correctly at the point where you add more than one delivery to a stop and you'll have to shave some time off each duration.
Whether the route is reversed or not, the duration of multiple deliveries at one stop will not change in order for you to reach the set arrival times.
No?

Ok, let me try a different approach. I want to able to reverse a route and have the stop duration times imported from my spreadsheet - that match each stop. You can see my try at it in the last few lines below...

Code:

    If objFindResults.ResultsQuality = geoNoResults Then  ' <> geoFirstResultGood Then      MsgBox ("Address incorrect on line " & nCurrentRow)
      Exit Sub
    End If
    
    Set objPushpin = objmap.AddPushpin(objLoc, szName)
            
    szStopTime = Cells(nCurrentRow, 15)
            
    Dim wyp As MapPoint.Waypoint
    Set wyp = objRoute.Waypoints.Add(objPushpin, objPushpin.Name)
    wyp.StopTime = szStopTime * geoOneMinute
    'wyp.StopTime = Range("Truck" & nTruck & "stop") * geoOneMinute
            
    Dim objShape As MapPoint.Shape
    objPushpin.BalloonState = geoDisplayName


    nCurrentRow = nCurrentRow + 1
    szAddress = Cells(nCurrentRow, 3)
    
  Loop
  
  Set oDS = objmap.DataSets("My Pushpins")
  oDS.Name = Application.ActiveSheet.Name
  
  objmap.Saved = True
  oApp.Visible = True
  objmap.DataSets.ZoomTo
  ' change all pushpins to small red circle
  objmap.DataSets(1).Symbol = 25
  objmap.Altitude = objmap.Altitude * 0.9
  oApp.PaneState = geoPaneRoutePlanner
    
  objRoute.Waypoints.Add objmap.FindAddressResults(ThisWorkbook.Worksheets("Settings").Range("EndAddress"), _
    ThisWorkbook.Worksheets("Settings").Range("EndCity"), , _
    ThisWorkbook.Worksheets("Settings").Range("EndState"), _
    ThisWorkbook.Worksheets("Settings").Range("EndZip"))(1), "Start"
    
  
  objRoute.Waypoints.Optimize
  objRoute.Reverse
  
'Adds stop time to reversed route


Dim nCount As Integer
nCount = objmap.ActiveRoute.Waypoints.Count


For i = 1 To nCount - 1
objmap.ActiveRoute.Waypoints.Item(i).StopTime = szStopTime * geoOneMinute
Next
  
  objRoute.Calculate

Hi Jim,

I don't see this code as modifying what I've stated so far.
However, I think that if the route is reversed, you might want to iterate the stop times in reverse as well.
That is,
For i = nCount - 1 To 1
'You'll need to work out nCurrentRow from bottom to top
szStopTime = Cells(nCurrentRow, 15)
objmap.ActiveRoute.Waypoints.Item(i).StopTime = szStopTime * geoOneMinute
Next

The problem is there is no reference to the stop time for each particular stop. When you are importing a route, you can reference each individual stop as you import it, thus you have the ability to have specific stop duration's for each delivery. When you reverse a route though, all of the information is already in mappoint and it does not reference excel again for it, and if it did it wouldn't know which stop is which.

An example would be:

3 deliveries

Bob's body shop
Bob's body shop
Jim's repair

Since Bob's is the same location, just 2 packages, my excel sheet inputs 10 minutes for one "Bob's" and 0 for the next one. The reason for this is our sheet also exports a map to a web server for our salespeople to look at and be able to tell our customers when they can expect to see their product. When you have 20 or so packages a day for each route, and many of those are multiple, it skews the amount of time spent at each location. As you can see in the example picture attached... in the route planner pane stops that have more than one invoice only have one stop duration listed. I would like to be able to reverse the route and keep those stop durations the same. Currently it will add the same stop duration to every delivery.

If I remove the duplicate locations in MapPoint, then the time works out fine obviously, but I also have a feature that prints a manifest for the driver and I want him to be able to have the invoice information etc on that manifest.

I may not be able to do what I want to do, and I may have to change how our manifest for the driver is printed.

Stop Times.jpg

Thanks for the help Michael

It can be done if you have your dataset set up for the desired operation.
Good Luck!

Michael - thanks to your help I think I have it worked out, need to test it on a few routes to be sure. Below is how I worked it out (just the section of code that performs the specific operation)... I am sure could be better, but it does work. It puts correct stop duration times on each stop and duplicates only have one stop time - thus maintaining the integrity of the route times overall.

Code:

  objRoute.Waypoints.Optimize  
  
'Export stops and stop times to use for refernce for rev route stoptimes
            
    Sheets("Revrt").Select
    Range("B1").Select
  For p = 1 To objRoute.Waypoints.Count
  ActiveCell.Offset(1, 0).Value = (objRoute.Waypoints.Item(p).StopTime / geoOneMinute)
  ActiveCell.Offset(1, 0).Select
            
Next
            
       Range("C1").Select
  For p = 1 To objRoute.Waypoints.Count
  ActiveCell.Offset(1, 0).Value = (objRoute.Waypoints.Item(p).Name)
  ActiveCell.Offset(1, 0).Select


Next
               
objRoute.Reverse


'Adds stop time to reversed route
    Sheets("Revrt").Select
    Dim nCount As Integer
    Dim LastRow As Long
    With ActiveSheet
        LastRow = .Cells(.Rows.Count, "C").End(xlUp).Row
    End With
    nCount = objmap.ActiveRoute.Waypoints.Count
   


For i = 1 To nCount - 1
szjStopTime = Cells(LastRow, 2)
objmap.ActiveRoute.Waypoints.Item(i).StopTime = szjStopTime * geoOneMinute
LastRow = LastRow - 1
Next
  
  objRoute.Calculate