Trip duration

I have an Excel user defined function to return the round trip distance between three way points (the start and end waypoints are the same address). I know Mappoint will report a trip duration in hours and minutes once the "get directions" icon is involked. Is there a way to return the trip duration programmatically using VBA in Excel. I have searched through the Mappoint Object Model but don't find any memebers that would return trip duration.

Thanks for any help!

Trebormac

Look some more. I know there is.... I have used it :redface:

Take a look at the TripTime Property of the Route Object.

Eric

Thanks Eric,

I did dig a little deeper and found "Driving Time" and "Trip Time", both members of the Route Class....I used the driving time method. With assistance from the MapPoint Help File I was able to create the VBA code I needed.

Fot those interested I have attached the code as a text file. This is in the form of a user defined function in Excel, where I need to compute the driving time from a fixed location (address) to several dozen separate locations. Hence I hard coded the starting point in the code. This of course can be modified if there is no fixed starting point by using multiple input parameters in the function call, e.g., sAddress2, sAddress3, etc. and adding lines of code for each objRoute.Waypoint.FindResults(sAddress2...)

The separate location addresses are in cells in a column. Invoke the function in a cell adjacent to the column containing the separate addresses, point it to the first address and hit OK. Then copy it down the column. If there a lot of addresses it may take some time to calculate.

Thanks again,

Trebormac

Very cool, thanks for sharing the code. I am also posting the code in here in case anyone has trouble getting to the attachment.

Option Explicit

Dim oApp As MapPoint.Application

Function GetDuration(sAddress1 As String)
Dim objMap As Map
Dim objRoute As Route

If oApp Is Nothing Then
Set oApp = CreateObject("MapPoint.Application")
End If

oApp.Visible = False
Set objMap = oApp.NewMap
Set objRoute = objMap.ActiveRoute

'Add route stop and calculate the route
objRoute.Waypoints.Add objMap.FindResults("69 West Washington Street, Chicago, IL 60606").Item(1)
objRoute.Waypoints.Add objMap.FindResults(sAddress1).Item(1)
objRoute.Calculate

GetDuration = objRoute.DrivingTime / geoOneMinute

objMap.Saved = True

End Function