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