I'm trying to adapt an old post I found from 2007 but get an error message on line 2 - Dim objApp As New MapPoint.Application- "compile error - user-defined type not defined".:

Can anyone explain why this is?

I have a list of origin stores and destination stores in an Excel worksheet.

Many thanks Keith

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

Loop

End Sub

You need to add a reference to the MapPoint objects. This will be in the project references rather than the source code.

At the moment, it doesn't not recognise MapPoint - so you have to tell it what the API objects are and where they are. Adding the reference does this.

Richard

Great, found it thanks for your help.