Welcome to MapForums!

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

## Calculate driving distance matrix in Excel

This is a discussion on Calculate driving distance matrix in Excel within the MapPoint Desktop Discussion forums, part of the Map Forums category; Hi Maddog, I read your post from a couple of years back. For my graduation project I have to research ...

1. Junior Member White Belt
Join Date
Apr 2011
Posts
1

## Re: Calculate driving distance matrix in Excel

I read your post from a couple of years back. For my graduation project I have to research the Vehicle Routing Problem for a company. The thing is that I need to create a distance matrix for all their customers, which is quite time consuming. Do you still have the Excel model? This would really help me out a lot!

Dave, I've built an Excel model that does just the thing

You input the Lat/longs and it outputs the following:

- a map of all the points
- a straight-line matrix
- a speed matrix
- a time matrix
- and a 'wiggle' matrix (road/straight-line)

You can also select whether you want miles/kms, shortest/quickest, mins/hours and so on, plus drivetime zones for the map itself.

Send me an email and I'll send you the model. If it's for personal use then I won't charge...

2. ## Re: Calculate driving distance matrix in Excel

MileCharter continues to be available. Free trial available from MileCharter: Home Page

Richard

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

## Re: Calculate driving distance matrix in Excel

Dave, I've built an Excel model that does just the thing

You input the Lat/longs and it outputs the following:

- a map of all the points
- a straight-line matrix
- a speed matrix
- a time matrix
- and a 'wiggle' matrix (road/straight-line)

You can also select whether you want miles/kms, shortest/quickest, mins/hours and so on, plus drivetime zones for the map itself.

Send me an email and I'll send you the model. If it's for personal use then I won't charge...

My name is John and I am currently studying at Warwick Universityfor postgraduate. I am working on the final project which required calculating the distance matrix and time matrix based between UK post code. The supervisor asked me to do this using Mappoint.

By chance, I noticed your posting on this forum which is obviously 7 years back. I hope my reply is not too late. Your excel model is definitely what I am after. If you can kindly share your model with me, you cannot image how much time I will save and I cannot thank you enough!

So, dear sir, Can you send me a copy of your model to me. I made a solemn commitment that this model is only for personal and academic use and I will remove this model as soon as I completed my project. You can send the model to john453@live.cn Thanks again!

4. ## Re: Calculate driving distance matrix in Excel

Also note that MileCharter has a free trial

5. Junior Member White Belt
Join Date
Aug 2011
Posts
1

## Re: Calculate driving distance matrix in Excel

Hi There - It seems an old discussion thread. If this message reaches you, can I take look at your model? Thanks

Dave, I've built an Excel model that does just the thing

You input the Lat/longs and it outputs the following:

- a map of all the points
- a straight-line matrix
- a speed matrix
- a time matrix
- and a 'wiggle' matrix (road/straight-line)

You can also select whether you want miles/kms, shortest/quickest, mins/hours and so on, plus drivetime zones for the map itself.

Send me an email and I'll send you the model. If it's for personal use then I won't charge...

6. Junior Member White Belt
Join Date
Jan 2012
Posts
1

## Re: Calculate driving distance matrix in Excel

I too would like a copy of your program if that was possible?...Its not for commercial use and not for a college project either )...just out of interest to see how these thing are done.

If you want to PM me that would be great.

Thanks

7. Member Green Belt
Join Date
Sep 2009
Location
Posts
94

## Re: Calculate driving distance matrix in Excel

Hi,

I you register, you will get a 3 month free license.

Kind regards
Manfred

8. Junior Member White Belt
Join Date
Nov 2011
Posts
5

## Re: Calculate driving distance matrix in Excel

OP

how are you with VBA in excel?

i have a bit of code which will run travel distance to a location from a location

(in my case i was using postcodes, but can be used with lat long with a minor code tweak)

i used it to calculate for a business model i was working on the distance between every postcode district to every postcode district, take a long time but worth it.

sounds like your problem would be a doddle compared to that

here is the code if you are familiar with VBA shoudlnt be too hard to work around, note map type and my i which was my 110 locations to work out distance to, hope it helps someone. note also szzip1 and 2 pretty sure you can adapt this for lat long

Code:
```Sub distance()
Dim objApp As MapPoint.Application
Dim objmap As Map
Dim objloc As Location
Dim szzip1 As String
Dim szzip2 As String
Dim objroute As Route
Dim i As Integer
Dim x As Integer
i = 3
x = 3
Set objApp = CreateObject("MapPoint.Application")
objApp.Visible = True
Set objmap = objApp.NewMap
Set objroute = objmap.ActiveRoute

Do While x < 2894 And i < 110

szzip1 = Sheets("sheet1").Cells(2, i).Value
szzip2 = Sheets("sheet1").Cells(x, 1).Value
On Error Resume Next

'Add route stops and calculate the route
objroute.Calculate

Sheets("sheet1").Select
Cells(x, i).Value = objroute.distance
'Range("B" & x).Value = objroute.distance

objroute.Clear
objroute = Nothing
x = x + 1

If x = 2894 Then
x = 3
i = i + 1

End If

Loop

End Sub```
Last edited by Eric Frost; 01-06-2012 at 08:41 AM.

9. ## Re: Calculate driving distance matrix in Excel

FindResults returns an object and not a collection - you should check the status of this collection before blindly calling Item(1). There might not be an item 1, and the above code will throw an exception (fatal error).

10. Junior Member White Belt
Join Date
Nov 2011
Posts
5

## Re: Calculate driving distance matrix in Excel

Originally Posted by Winwaed
FindResults returns an object and not a collection - you should check the status of this collection before blindly calling Item(1). There might not be an item 1, and the above code will throw an exception (fatal error).
i use the above code without any problems,

the error handler you could write in more detail however in this case it is a simple if its not there move onto the next one

in the OP question he has lat long details since i am assuming most locations will have a lat long data you can blindly call the data.

i dont think you are reading this code how i use it a top list(i) of postcodes along an excel column and x is a row list of postcodes checking distances between each one

the code is fine its quick and dirty

Page 2 of 3 First 123 Last