Welcome to MapForums!

When you are signed in, this message, the ads in this row, and the red-underlined link ads all go away.

## How to generate drive time matrix rathe than distance matrix in EXCEL

This is a discussion on How to generate drive time matrix rathe than distance matrix in EXCEL within the MapPoint Desktop Discussion forums, part of the Map Forums category; Dear all: Now, I am quite clear about how to generate distance matrix based on post code using EXCEL automatically. ...

1. Junior Member White Belt
Join Date
Jul 2011
Posts
9

## How to generate drive time matrix rathe than distance matrix in EXCEL

Dear all:

Now, I am quite clear about how to generate distance matrix based on post code using EXCEL automatically. The code is in the follows and it turned out to be quite successfully. However, Now I would like to generate drive time between each post code rather distance. So, in this case. how to properly modify the code in order to make that? Moreover, if this can be done, is it possible to modify the settings of the speed?

I cannot thank you enough if you can give me help!!

"
Sub DistanceMatrix_Generator_Click3()

Set oApp = CreateObject("MapPoint.Application.EU.16")
oApp.Visible = False
Set objMap = oApp.NewMap
Set objRoute = objMap.ActiveRoute
oApp.ActiveMap.Saved = True

For i = 3 To 11

For j = i + 1 To 12

szZip1 = Worksheets("Sheet1").Cells(i, 2)
szZip2 = Worksheets("Sheet1").Cells(j, 2)

objRoute.Calculate

Worksheets("Sheet1").Cells(i, j) = objRoute.Distance

Next j

Next i

objRoute.Clear
oApp.ActiveMap.Saved = True

End Sub"

2. ## Re: How to generate drive time matrix rathe than distance matrix in EXCEL

Awesome!! A question I can answer and not involving DEP, Windows 7, Visual Studio 2010, or 64-bit OS's

It's actually pretty simple, just use the DrivingTime property, here's the example from the help file --

Code:
```  Sub GetRouteDrivingTime()

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 the route
objRoute.Calculate

'Find the driving time of the route
MsgBox "Driving time in minutes: " + CStr(objRoute.DrivingTime / geoOneMinute)

End Sub```
Speed is set using the DriverProfile object --

Code:
```  Sub ChangeLimitedAccessSpeed()

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 the route

objRoute.Calculate

End Sub```
hope this helps!

Eric
Last edited by Eric Frost; 08-11-2011 at 02:23 PM.

3. Junior Member White Belt
Join Date
Jul 2011
Posts
9

## Re: How to generate drive time matrix rathe than distance matrix in EXCEL

Dear Eric:

Thank you so much for what you have done for me!

However, now, I have a more complex question. since the distance is worked out and It is known that the road had five types of road which all called RoadInterstate, limited-access highway, RoadOtherHighway,RoadArterial,RoadStreet respectively. This distance, for example, is 50 miles. Then 50 miles cover the overall length of each type of road.

So, I was wondering if there is any function or application in Mappoint to work out the distance for each type. As I am working on one project which requires manipulating the speed on each type of the road, so, I am quite desperate to find out how the overall distance can be split?

I cannot thank you enough!

4. ## Re: How to generate drive time matrix rathe than distance matrix in EXCEL

I'm not sure what you want is possible

But I'm not sure you can actually get the speed or type of road at each point.

Maybe if you have a real road dataset such as NAVTEQ's and a real GOS platform such as MapInfo Pro, but I don't think you can really extract the road types from MapPoint.

Anyone have thoughts on this?

Thanks!
Eric