PDA

View Full Version : Total State Miles



Fog Line
07-01-2013, 01:18 PM
Trying to find a way to get Total State Miles. Run a route from point A to point B. Say Atlanta, GA. to Ontario, CA. I need Total State Miles.

Eric Frost
07-03-2013, 03:39 PM
Fog Line,

Welcome to the forum!

This can be done with a custom macro.. I prepared something for a CPA client earlier this year that actually calculated mileage by county, calculating mileage by State is actually a lot easier.

Are you versed in any Excel macro programming? Would you be interested in some training on how to prepare this or would you like me to do for you? It's fairly simple either way, probably just 1-2 hours.

Eric

Fog Line
10-11-2013, 10:43 AM
Hello Eric Thanks for your reply. Yes I can do Excel macro programming. Do you have a sample of it I Excel? If I see it I should be able to make it work in MS Access. You can send it to my email also if you like.

Fog Line
10-11-2013, 10:44 AM
Fog Line,

Welcome to the forum!

This can be done with a custom macro.. I prepared something for a CPA client earlier this year that actually calculated mileage by county, calculating mileage by State is actually a lot easier.

Are you versed in any Excel macro programming? Would you be interested in some training on how to prepare this or would you like me to do for you? It's fairly simple either way, probably just 1-2 hours.

Eric
Hello Eric Thanks for your reply. Yes I can do Excel macro programming. Do you have a sample of it I Excel? If I see it I should be able to make it work in MS Access. You can send it to my email also if you like.

Eric Frost
10-11-2013, 11:17 AM
Check the Direction object documentation in the help file. There is an ElapsedDistance Property you can use to keep track of the mileage. I didn't test the code below but it should work.

Scan the Instructions to see if there is a specific string that tells you when you are entering a new state.



Sub EnumerateDirections()

Dim objApp As New MapPoint.Application
Dim objMap As MapPoint.Map
Dim objRoute As MapPoint.Route
Dim DIR As MapPoint.Direction

'Set up the application
Set objMap = objApp.ActiveMap
Set objRoute = objMap.ActiveRoute
objApp.Visible = True
objApp.UserControl = True

'Add route stops and calculate the route
objRoute.Waypoints.Add objMap.FindResults("Seattle, WA").Item(1)
objRoute.Waypoints.Add objMap.FindResults("Redmond, WA").Item(1)
objRoute.Calculate

For Each DIR in objRoute.Directions
Debug.Print DIR.ElapsedDistance & "-" & DIR.Instruction
Next
End Sub

Eric Frost
10-11-2013, 07:48 PM
By the way, that code would work in Excel or Access equally well, you would just want to interact with data in either one.

There are some recent articles for working with MapPoint in Access VBA!

MP2K Magazine Articles (http://www.mapforums.com/mp2k-magazine-articles/)

http://www.mapforums.com/access-vba-programming-part-i-geocoding-mappoint-28228.html

http://www.mapforums.com/access-vba-programming-part-ii-calculating-distance-matrix-28235.html

Hope this helps!

Eric