View Full Version : Calculate route

07-23-2012, 02:22 AM
I've found VBA code to calculate the route between two locations:

Dim oMap As MapPoint.Map
Dim oRte As MapPoint.Route
Set oMap = GetObject(, "MapPoint.Application").ActiveMap
oMap.Parent.PaneState = geoPaneRoutePlanner
Set oRte = oMap.ActiveRoute
With oRte.Waypoints
.Add oMap.FindAddressResults(, "New Bedford", , _
.Add oMap.FindAddressResults(, "Mystic", , "Conn")(1)
End With
'Zoom to the route

How do I modify the code to determine the route between my imported locations? My imported locations are represented within pushpins, tblWellBData & tblWellAData.

Thank you in advance!!

07-23-2012, 08:04 AM
The pushpin object has a Location property - add the contents of this instead of blindly extracting a location from a FindResults collection.

Something like:

oRte.Waypoints.Add( myPushpin.Location )

07-23-2012, 09:38 AM
In my developement, each pushpin set only has 1 value. Later, 1 of the pushpin sets will have a lot of values. So to start, how do I get my values into (myPushpin.location)?

Eric Frost
07-23-2012, 09:49 AM
You can loop over all the records in a dataset. Something like this.

Dim mp As MapPoint.Map
Dim ds As MapPoint.Dataset
Set ds = oMap.Datasets("My Dataset")

Dim rs As MapPoint.Recordset
Set rs = ds.QueryAllRecords


Do While Not rs.EOF
MsgBox rs.Name

Also, see the examples in the help file.

Does this help?


07-23-2012, 10:00 AM
Yes, helps tremendously!! Last questions, by what you wrote, rs.Name will be the individual pushpin, what's the syntax for inserting this into the statement (myPushpin.location)

Eric Frost
07-23-2012, 10:09 AM
I don't mean to sound dumb, but I think it depends on what you are wanting to do?

Do you want to having one starting location and get a route to each of the other points in another dataset? But for that particular line of code it would look something like this --

oRte.Waypoints.Add rs.Pushpin.Location, rs.Name

See in the help file for "Add method (Waypoints)"

07-23-2012, 10:18 AM
Yes, that's exactly what it will be. One pushpin set has 1 location. The other pushpin set will have many locations. From the 1 location, I'm needing to determine the distances between that location and all the other locations. I'll put those distances in an Access table, then order them by shortest distance to longest distance. Then the user will select the ending location based on distance, then I'll display the route.

I think what you've provided will get me there. Thanks!!

Eric Frost
07-23-2012, 10:35 AM
Fantastic, glad to help!

When you get it working, if you are interested in sharing a simple solution & write-up as an article for MP2K Magazine I'm sure that would be much appreciated by other readers :ph34r:

07-23-2012, 10:50 AM
Thank you again. I have to do my regular job today, so I won't be able to work on this today.

I'm doing all this within MS Access. To give you the complete story, the data is a list of operators. Each operator has multiple locations. The user selects the origin operator, then the origin location. So that is pushpin set A. The user selects the destination operator. Pushpin set B will contains the locations for set B. Using MapPoint, I then will determine the distances between set A and set B, display them in distance order, then the user will select the distance, I then will display the route. All of this being done in Access.

When I get it completed, I'll post my work.

07-25-2012, 12:55 AM
This is what I have at this time. It's not completed yet, but I've been able to calculate the distance between 2 pushpins within my datasets. I do have 1 question. Within my import, I'm using the following fields: name, hame2, address 1, address 2, address 3, county, latitude, longitude. Concerning the waypoints, how do I retrieve the value that is within address 3 for the pushpin? Here's all my code

Dim objApp As New MapPoint.Application
Dim objMap As MapPoint.Map

Set objMap = objApp.ActiveMap
Set objRoute = objMap.ActiveRoute
objApp.Visible = False
objApp.UserControl = False

Dim objDataSets As MapPoint.DataSets
Dim objDataSetA As MapPoint.DataSet
Dim objDataSetB As MapPoint.DataSet
Dim zDataSource As String

zDataSource = "C:\Production\George Walker\Wellinfo.accdb!tblWellAData"
Set objDataSets = objMap.DataSets
Set objDataSetA = objDataSets.ImportData(zDataSource)
zDataSource = "C:\Production\George Walker\Wellinfo.accdb!tblWellBData"
Set objDataSets = objMap.DataSets
Set objDataSetB = objDataSets.ImportData(zDataSource)

Dim rsWellA As MapPoint.Recordset
Set rsWellA = objDataSetA.QueryAllRecords

Dim rsWellB As MapPoint.Recordset
Set rsWellB = objDataSetB.QueryAllRecords

' Add those addresses as Waypoints
Dim oWps As MapPoint.Waypoints
Set oWps = objRoute.Waypoints

objRoute.Waypoints.Add rsWellA.Pushpin

Do While Not rsWellB.EOF
objRoute.Waypoints.Add rsWellB.Pushpin
MsgBox CStr(objRoute.Distance)

objApp.ActiveMap.Saved = True
Set objApp = Nothing

07-25-2012, 01:04 PM
I came up with my own solution, I changed the data fields within the import structure to have the value which was previous in the address 3 field to be within the name field. My next question is how do I make the route and map available to be displayed in MS Access?

Eric Frost
07-26-2012, 07:20 AM
I think there's some examples of this in the articles on MP2Kmag.com, see the P.F. Chang's article and also the Home Locator MapPoint Articles - MP2K Magazine (http://www.MP2Kmag.com/articles.asp)

07-26-2012, 07:52 AM
Thanks. I'll take a look at these.

07-27-2012, 07:57 AM
In displaying the driving instructions, I'm using the route object, item property, instruction property. That displays what I need except it does not include the mileage for each instruction. How do I obtain that information?

Eric Frost
08-01-2012, 10:25 AM
I haven't played with this recently, but see the help file for the Direction object, there are properties Distance and ElapsedDistance . Does this help?

08-02-2012, 06:53 PM
The distance property shows it can display a distance for a segment, but I can't figure out how to use it. I can use the elapseddistance by subtracting from the previous instruction to get the segment distance. Another question. Within MS Access, if a query takes a long time, you can create a status indicator so the user knows that something is happening within the software. Is their anything like that within MapPoint. I sometimes do a loop which contains 3000 routes. It appears as though the computer is dead, but I know it's processing. What are your thoughts?

Eric Frost
08-02-2012, 08:44 PM
You're doing a loop, so couldn't you have some counter or progressbar in the loop?

08-02-2012, 09:58 PM
That's what I was thinking, but I can't figure it out.

Eric Frost
08-03-2012, 10:18 AM
You're working in Excel right? You could probably do this with a pop up form, but it's probably easier just to write to a cell.

Excel 2007 or 2010 added some cool conditional formatting you could use -- How to Create Progress Bars in Excel With Conditional Formatting - How-To Geek (http://www.howtogeek.com/howto/45677/how-to-create-progress-bars-in-excel-with-conditional-formatting/)

Search "Excel Progressbar" for other ideas.


10-15-2012, 05:50 AM
Well this site is perfect way to find the exact location when you are traveling. Last week I went for a mount erring and took calculate route and certainly manged to get the exact location and reached to my destination.

10-18-2012, 01:48 AM
Well I have no knowledge about calculate route but I could surely say that this forum is great platform from where you could get access to relevant information on this matter. Plus you could share your experiences as well.

Eric Frost
10-18-2012, 08:50 AM

What value are you adding to this forum with your posts? What are you getting out of it? Feel free to be honest.


01-29-2013, 10:39 AM
That's what I was thinking, but I can't figure it out.

Blimey, my first post and I get to post something useful for someone.....5 months late but nevermind.

Status bar in Excel:

Create a userform with a single text box and a single label. Make the label box almost the entire width of the userform and take note of the listed width in the properties box, you'll be needing it later, then set the background colour of the label box to something that'll stand out, whatever you find aesthetically pleasing.

Then, in your macro....BEFORE the loop begins you initialise the userform with:

userform1.textbox1.text = "Whatever title you want"
userform1.label1.width = 1
userform1.show vbmodeless

The userform1.label1.width = 1 command will shrink your status bar down to nothing.
Then, at the end of your loop, just before the Next or Loop or whatever style of loop you're using you put the following:

userform1.label1.width = ( original width of label when you designed the form / your end of loop value) * current loop value


userform1.label1.width = (450 / TotalRecords) * sweepnumber

The reason you "repaint" the form after updating the label width is simply to redraw it, Excel macro's will always run faster if you use application.screenupdating = false to suppress the screen output while the macro is running.....and, therefore, userforms won't update unless you tell them to.

Hope this is helpful.