MapForums

I have about 700 sites across the country that I have in a spreadsheet. I have imported them into MP 2004 and they actually all show up. I would like to know if there is an automated way to determine the distance and time between about 50 of these sites (hubs) and the rest of them based on which hubs are designated to support which sites? I would then like to export all of the info back into excel for analysis.

Thoughts?

Hi there

I think this is likely to be quite straightforward in Excel vba. I’m currently trying to improve my vb programming skills and used the following code for a similar application in the UK. Please note that the points were located via their postcodes and I knew all the postcodes were valid (ie. recognised by MP2006) ahead of running the program. The data was input as 2 columns in an excel worksheet with the following format

Hub Satellite Locations
KT17 4BT W1U7BU
SY22 6AA
E4 7JA BN1 4JF
NW1 8HX
OX29 7DX

etc,etc (sorry, I can't get this table to come out in the right format. The satellite postcodes W1U7BU, SY22 6AA, BN1 4JF, NW1 8HX, OX29 7DX should all be in the second column).

It ouputs drive distance, drivetime and straight-line distance to the same worksheet.
This is the code I used

Private Sub CommandButton1_Click()
Dim objApp As New MapPoint.Application
Dim objMap As MapPoint.Map
Dim objRoute As MapPoint.Route
Dim objLoc1 As MapPoint.Location
Dim objLoc2 As MapPoint.Location

Set objApp = CreateObject("MapPoint.Application.EU.13")
objApp.Visible = False

Set objMap = objApp.NewMap
Set objRoute = objMap.ActiveRoute
Worksheets("Excel Worksheet").Cells(1, 3).Value = "Drive Distance (kms)"
Worksheets("Excel Worksheet").Cells(1, 4).Value = "Drive Time (mins)"
Worksheets("Excel Worksheet").Cells(1, 5).Value = "Straight Line Distance (kms)"
NReadRow = 2
n = 2

Do While Worksheets("Excel Worksheet").Cells(NReadRow, 2) <> ""

'Locate the 2 points
Set objLoc1 = objMap.FindResults(Worksheets("Excel Worksheet").Cells(n, 1)).Item(1)
Set objLoc2 = objMap.FindResults(Worksheets("Excel Worksheet").Cells(NReadRow, 2)).Item(1)

'Calculate the route
objRoute.Waypoints.Add objLoc1
objRoute.Waypoints.Add objLoc2
objRoute.Calculate

'Drive Distance in kms
Worksheets("Excel Worksheet").Cells(NReadRow, 3) = objRoute.Distance
'Drive Time in minutes
Worksheets("Excel Worksheet").Cells(NReadRow, 4) = objRoute.DrivingTime
'Straight Line Distance in kms (as a check)
Worksheets("Excel Worksheet").Cells(NReadRow, 5) = objMap.Distance(objLoc1, objLoc2)
objRoute.Clear

'Assign the correct hub
NReadRow = NReadRow + 1
If Worksheets("Excel Worksheet").Cells(NReadRow, 1).Value <> "" Then
n = NReadRow
End If

Loop

End Sub

As I said I’m only learning so you need to treat the code with real caution; there are bound to be errors. But I hope it gets you started.

By the way you’d have got better exposure of your post if you’d put it in the MapPoint Discussion Forum.

Rgds, David

__________________
David
MapPoint Europe Gallery at http://www.broomanalysis.plus.com/gallerylist.html

Thanks for the reply. (I moved the thread also).

I will take a shot at putting your code in and seeing what happens.

Question... I have recently been upgraded to Office 2007. Might there be an issue with using Excel VBA from 2007 then saving down to 97-2003 workbook?

Thanks again David.