Hello everyone,

I have only recently started using MapPoint so my knowledge of this software is literally non-existant. My VBA knowldge, as you will notice, is also quite limited.

What I am trying to do is to simply enter data in Excel (country, city and post code) and then create a macro which will open MapPoint and enter the postcodes in RoutePlanner. From previous threads in this forum I found a VBA code which does exactly what i want but when I tried to use it, nothing is entered in RoutePlanner.

Code:

Sub Add_Address_To_Route_Planner()
'
' Add_Address_To_Route_Planner Macro


Set App = CreateObject("MapPoint.Application")
    App.Visible = True
    Set objMap = App.NewMap
    Set objRoute = objMap.ActiveRoute

    Row = 2
    While Cells(Row, 1) < "5"
    objRoute.Waypoints.Add objMap.FindAddressResults([Country], [City], [PostalCode])
    Cells(Row, 1) = [Country]
    Cells(Row, 2) = [City]
    Cells(Row, 3) = [PostalCode]
        
    Row = Row + 1
    Wend

MsgBox ("Click ok to retun to excel")

'
End Sub

Can someone plz have a look at the code and let me know what i am doing wrong?

Btw I am currently using Excel 2007 and MapPoint 2009.

Thank you very much

Can you post a sample spreadsheet with some data? (zip and attached to your post)

The row/col's referenced in the code need to match the worksheet, etc.

Eric

Thanks a lot for the prompt reply Eric.

The spreadsheet is attached on my reply.

Problem has been solved.

Thx for the help Eric

What does your code look like now? I made several changes.

First, I'd recommend to go to Tool | References and add a reference to MapPoint.

Also, Declare (Dim) the app and map objects (see below). This makes it much easier to work with the code; you get context-sensitive help.

Code:

Sub Add_Address_To_Route_Planner()

    Dim App As MapPoint.Application
    Dim objMap As MapPoint.Map
    
    Set App = CreateObject("MapPoint.Application.EU")
    App.Visible = True
    Set objMap = App.NewMap
    Set objRoute = objMap.ActiveRoute

    Row = 2
    While Row < 5
    objRoute.Waypoints.Add objMap.FindAddressResults(, Cells(Row, 2), , , Cells(Row, 3), Cells(Row, 1))(1)
    Row = Row + 1
    Wend

End Sub

thx for providing the code Eric. i also have to say that you have done an amazing job in providing so many tutorials on how to operate mappoint from excel. they were really helpful.

the code that i have used is

Code:

Dim mp As MapPoint.Application
Sub Macro1()
'
' Macro1 Macro
'
Set mp = CreateObject("mappoint.application.eu.16")
mp.Visible = True
Set objMap = mp.newmap
Set objroute = objMap.activeroute

Row = 2

While Cells(Row, 5) <> ""
objroute.Waypoints.Add objMap.FindAddressResults( _
Cells(Row, 1), _
Cells(Row, 2), _
Cells(Row, 3), _
Cells(Row, 4), _
Cells(Row, 5)).Item(1)

Row = Row + 1

Wend

End Sub

i have two more questions though and i wondering if you can help me:

1) do you know what algorithm does MapPoint use to solve TSP problems? I have used the "Optimise Stops" options and i was impressed with the results even though it iseems to be quite slow.

2) is there any way of constructing a distance matrix in Excel by using the data provided from MapPoint? Ideally i would like to do that without the need to install any more add-ins

#1, no I do not sorry...

#2, for a distance matrix, yes you could do this with some Excel VBA code, but it would be easier to use something off the shelf. Have you explored some tools thus far? I'm not sure which would be more appropriate - MPMileage or MPMileCharter?

Eric

For a distance matrix, you mean between two groups of pins? The Basic License of MPMileCharter would do this ( MPMileCharter: Home Page ). Note you are going to need to either do it by hand or with API coding - whether your own or purchased.Purchasing saves you the time it takes to fix problems in your code!

Note that the above code assumes FindAddressResults returns a valid location - and in the case of multiple locations, the first one is the correct one (this is rarely the case). You should check the ResultsQuality property of the object returned by FindAddressResults, before blindly inserting the first entry (which might not even exist!) into your route.

Originally Posted by Eric Frost

#2, for a distance matrix, yes you could do this with some Excel VBA code, but it would be easier to use something off the shelf. Have you explored some tools thus far? I'm not sure which would be more appropriate - MPMileage or MPMileCharter?

Eric

I would prefer to write my own VBA code since I am not sure if I can purchase a dedicated tool for calculating distance matrices. If things turn ugly on the way though I will consider it more seriously

Originally Posted by Winwaed

For a distance matrix, you mean between two groups of pins? The Basic License of MPMileCharter would do this ( MPMileCharter: Home Page ). Note you are going to need to either do it by hand or with API coding - whether your own or purchased.Purchasing saves you the time it takes to fix problems in your code!

Note that the above code assumes FindAddressResults returns a valid location - and in the case of multiple locations, the first one is the correct one (this is rarely the case). You should check the ResultsQuality property of the object returned by FindAddressResults, before blindly inserting the first entry (which might not even exist!) into your route.

When I say distance matrix I mean derive a matrix which will calculate the distances between all of the postcodes to be used in the model (yes it will most probably be huge). This is because I will need such a matrix for further analysis.

With regards to the FindAddressResults returning an invalid location, that was something that I also noticed and couldn't understand why it was happening since the postcodes I was entering appeared to be correct. I eventually learned to check the results produced every time just in case MapPoint starts acting all weird again. Thank you for the tip though.