Trying to integrate Excel data into MapPoint

OK... I'm new to the whole data integration thing when it comes to MapPoint and Excel. I work for a trucking company that uses Excel to plan routes for trucks. They enter the data manually into Excel, then, manually enter much of the same data into MapPoint to calculate mileage and delivery times. After that, they enter the resultant MapPoint data into Excel again. (don't get me started on their OTHER inefficiencies)

What I would like to do is create a macro that does much of this automatically. OR, at the very least, exports Excel data to MapPoint in one step. Then, the data could be evaluated from there.

What requirements does MapPoint have for the dataset? Can I get away with just importing zip codes and having MapPoint create mileage info from there? That would be ideal.

Essentially, where do I start? :v2v:

Any helpful input would be appreciated.

- co

OK... I've manually accomplished the following. Hopefully someone will see what I'm doing and help provide a push in the right direction.

Using Zip Codes from a modified version of our load planner spreadsheet, I was able to use the Data import wizard to import push-pins that seem to show the appropriate locations. That data is sufficient for planning mileage and route information. I'm still trying to figure out how to plan a route using pushpins though. The search continues.

I have questions though:
How do you tell MapPoint to look at a specific subset of the data to import? ie: a specific cell range containing a list of zip codes that are already in the appropriate order.

Any thoughts? I know the MapForums hive mind already has some genius who has figured all this out, so I'm sharing my progress here in hopes that that person will have some good input. :)

TIA, again.

Have you seen the Excel / MapPoint examples at

Working With Excel and MapPoint - MP2K Magazine (http://www.mp2kmag.com/excel/) ?

Also, the Help file programming examples are awesome.

If / when you get stuck after that, please post your code and/or attach your spreadsheet, from there I think we can jump in more easily to help.

Eric

I'm in the same boat as you and trying to figure out a way to do the same exact thing you are. Hopefully we'll get some help.

Eric Frost: Have you seen the Excel / MapPoint examples at link ... Why no, I hadn't seen examples there. That's awesome. Thank you so much for the link. I'll look through all those in a few and hopefully come up with a solution (or at least get a few more steps along the process).

Thanks again!

- co

OK... awesome... I've gone through the first two tutorials. Those helped a great deal.

I'm working on incorporating the info I've gleaned on those into the current project. I think I'm getting the hang of the starting point on this at least. I need to have this process work where only the zip codes (10 to 15 of them) on an individual load are entered into MapPoint. Then, MapPoint would create a route in the order of the zip codes and output mileage information that goes back into Excel. But this is a significant first step...

I can see that your code identifies specific cell references using a coordinate system:
Worksheets("Sheet1").Cells(2, 2), _
Worksheets("Sheet1").Cells(2, 3), , _
Worksheets("Sheet1").Cells(2, 4), _
Worksheets("Sheet1").Cells(2, 5))(1) I wonder if it's possible to create an expression defining a range of cells and that includes an expression that could exclude that cell if it's empty, like if the list ends at 12 zip codes instead of the 15 cells that could have a zip code in them.

Also, when I take out the first line "Worksheets("Sheet1").Cells(2, 2), _" it goes all weird. Evidently, it can't identify the location using JUST the City, State and Zip information. That's a bummer.

- co

EDIT: OK, I'm an idiot. I see you have a solution for part of my whining. I'll read further before venting more of my spleen. :)

Looking forward to your next vent, maybe we can aid you along... :sultan: or not :bandana:. but don't hesitate to post details :singer: anyway.. someone else searching could also learn from your journey.

Eric

Eric, you ARE a rock-star. :)

I've done some minor modification on this code to deal with multiple zip codes.

Here's what I have so far.
Private Sub CommandButton1_Click()

Set oApp = CreateObject("MapPoint.Application.NA.13")
oApp.Visible = True
Set objMap = oApp.NewMap
Set objRoute = objMap.ActiveRoute

szZip1 = Worksheets("Sheet1").Cells(2, 1)
szZip2 = Worksheets("Sheet1").Cells(3, 1)
szZip3 = Worksheets("Sheet1").Cells(4, 1)
szZip4 = Worksheets("Sheet1").Cells(5, 1)
szZip5 = Worksheets("Sheet1").Cells(6, 1)
szZip6 = Worksheets("Sheet1").Cells(7, 1)
szZip7 = Worksheets("Sheet1").Cells(8, 1)
szZip8 = Worksheets("Sheet1").Cells(9, 1)
szZip9 = Worksheets("Sheet1").Cells(10, 1)
szZip10 = Worksheets("Sheet1").Cells(11, 1)
szZip11 = Worksheets("Sheet1").Cells(12, 1)
szZip12 = Worksheets("Sheet1").Cells(13, 1)
szZip13 = Worksheets("Sheet1").Cells(14, 1)

'Add route stops and calculate the route
objRoute.Waypoints.Add objMap.FindResults(szZip1).Item(1)
objRoute.Waypoints.Add objMap.FindResults(szZip2).Item(1)
objRoute.Waypoints.Add objMap.FindResults(szZip3).Item(1)
objRoute.Waypoints.Add objMap.FindResults(szZip4).Item(1)
objRoute.Waypoints.Add objMap.FindResults(szZip5).Item(1)
objRoute.Waypoints.Add objMap.FindResults(szZip6).Item(1)
objRoute.Waypoints.Add objMap.FindResults(szZip7).Item(1)
objRoute.Waypoints.Add objMap.FindResults(szZip8).Item(1)
objRoute.Waypoints.Add objMap.FindResults(szZip9).Item(1)
objRoute.Waypoints.Add objMap.FindResults(szZip10).Item(1)
objRoute.Waypoints.Add objMap.FindResults(szZip11).Item(1)
objRoute.Waypoints.Add objMap.FindResults(szZip12).Item(1)
objRoute.Waypoints.Add objMap.FindResults(szZip13).Item(1)
objRoute.Calculate

Worksheets("Sheet1").Cells(2, 3) = objRoute.Distance

End SubObviously, this code only works if cells 2 through 14 (in column 1) are populated. Once one of them isn't populated, the whole thing goes poop.

But if I can come up with a better way to define that column based on them being populated or NOT populated, THEN run a variation of that expression from there. .... PROFIT!

:)

- co

I don't think so, but

Thanks! :cowboy2:

Can you post a sample spreadsheet as an attachment? You might have to zip it first..

Eric

OK... here's a rough example of the sheet the boss-man uses.

http://checkedout.org/MapPointForum/SampleSheet.xls

The data I would like to automatically fill is the light blue fields in J6:J19
Hopefully this gives you an idea of what I'm dealing with.

- co

I'm not sure if I can help with the bigger picture or not, but we have a product (RouteWriter) which helps with the pushpins <-> MapPoint routes side of things, rather than the Excel Import side of things.

RouteWriter writes the pushpins to Excel in route order - ie. you've created your route, and you just want the order of the pins (and the other pin data).
RouteWriter includes RouteReader which can create a route from existing pushpins, inserting the pins in a pre-defined order. The order is set using a data field (eg. a sequence number). Note that manually importing pins (as above) does not allow you to add custom data fields - you have to use one of MapPoint's wizards; but this probably academic because if you're adding the pins programmatically, you can also insert them in any order you feel like.

RouteWriter: Export Routes from Microsoft MapPoint (http://www.routewriter.com)


Richard

So, you're trying to sell me a $75 license to your software that doesn't do what I want to do?

- co

Hi co,

I believe Richard was responding to your initial post:

"They enter the data manually into Excel, then, manually enter much of the same data into MapPoint to calculate mileage and delivery times. After that, they enter the resultant MapPoint data into Excel again. (don't get me started on their OTHER inefficiencies)"

Note that developers charge double that cost PER HOUR, eh.

:freak:

Mike Mattys

Yes I was looking at your original post. Often it is instructive to find out what a user is really trying to do - often there is a simpler approach.


Richard

UPDATE: OK, I've gotten this far.

I'm able to get it to calculate the total route mileage based on each individual point on the route then I'm using Excel to calculate the individual segment mileage. From there, Excel can calculate the number of hours (rounded up) for that segment. Then, inserting a start time SHOULD give me my scheduling information. What I'm missing is some process that looks for a null value in the excel spreadsheet zip code column and STOPS the process there.

Please see my totally inept attempt at showing what I mean in the code below. Note that I've rem'd those lines out. But it should tell you essentially what I want it to do. Any suggestions on how to phrase that so my computer knows what I'm talking about? I know next to nothing about vb scripting so I'm hoping someone might know.

Here's what I want it to do... sort of: If Worksheets("Sheet1").Cells(4, 1) = "" Then End Sub

Any help would be appreciated.

Private Sub CommandButton1_Click()

Set oApp = CreateObject("MapPoint.Application.NA.13")
oApp.Visible = True
Set objMap = oApp.NewMap
Set objRoute = objMap.ActiveRoute

szZip1 = Worksheets("Sheet1").Cells(2, 1)
szZip2 = Worksheets("Sheet1").Cells(3, 1)
objRoute.Waypoints.Add objMap.FindResults(szZip1).Item(1)
objRoute.Waypoints.Add objMap.FindResults(szZip2).Item(1)
objRoute.Calculate
Worksheets("Sheet1").Cells(3, 3) = objRoute.Distance

'If Worksheets("Sheet1").Cells(4, 1) = "" Then End Sub
szZip3 = Worksheets("Sheet1").Cells(4, 1)
objRoute.Waypoints.Add objMap.FindResults(szZip3).Item(1)
objRoute.Calculate
Worksheets("Sheet1").Cells(4, 3) = objRoute.Distance

'If Worksheets("Sheet1").Cells(5, 1) = "" Then End Sub
szZip4 = Worksheets("Sheet1").Cells(5, 1)
objRoute.Waypoints.Add objMap.FindResults(szZip4).Item(1)
objRoute.Calculate
Worksheets("Sheet1").Cells(5, 3) = objRoute.Distance

'If Worksheets("Sheet1").Cells(6, 1) = "" Then End Sub
szZip5 = Worksheets("Sheet1").Cells(6, 1)
objRoute.Waypoints.Add objMap.FindResults(szZip5).Item(1)
objRoute.Calculate
Worksheets("Sheet1").Cells(6, 3) = objRoute.Distance

'If Worksheets("Sheet1").Cells(7, 1) = "" Then End Sub
szZip6 = Worksheets("Sheet1").Cells(7, 1)
objRoute.Waypoints.Add objMap.FindResults(szZip6).Item(1)
objRoute.Calculate
Worksheets("Sheet1").Cells(7, 3) = objRoute.Distance

'If Worksheets("Sheet1").Cells(8, 1) = "" Then End Sub
szZip7 = Worksheets("Sheet1").Cells(8, 1)
objRoute.Waypoints.Add objMap.FindResults(szZip7).Item(1)
objRoute.Calculate
Worksheets("Sheet1").Cells(8, 3) = objRoute.Distance

'If Worksheets("Sheet1").Cells(9, 1) = "" Then End Sub
szZip8 = Worksheets("Sheet1").Cells(9, 1)
objRoute.Waypoints.Add objMap.FindResults(szZip8).Item(1)
objRoute.Calculate
Worksheets("Sheet1").Cells(9, 3) = objRoute.Distance

'If Worksheets("Sheet1").Cells(10, 1) = "" Then End Sub
szZip9 = Worksheets("Sheet1").Cells(10, 1)
objRoute.Waypoints.Add objMap.FindResults(szZip9).Item(1)
objRoute.Calculate
Worksheets("Sheet1").Cells(10, 3) = objRoute.Distance

'If Worksheets("Sheet1").Cells(11, 1) = "" Then End Sub
szZip10 = Worksheets("Sheet1").Cells(11, 1)
objRoute.Waypoints.Add objMap.FindResults(szZip10).Item(1)
objRoute.Calculate
Worksheets("Sheet1").Cells(11, 3) = objRoute.Distance

'If Worksheets("Sheet1").Cells(12, 1) = "" Then End Sub
szZip11 = Worksheets("Sheet1").Cells(12, 1)
objRoute.Waypoints.Add objMap.FindResults(szZip11).Item(1)
objRoute.Calculate
Worksheets("Sheet1").Cells(12, 3) = objRoute.Distance

'If Worksheets("Sheet1").Cells(13, 1) = "" Then End Sub
szZip12 = Worksheets("Sheet1").Cells(13, 1)
objRoute.Waypoints.Add objMap.FindResults(szZip12).Item(1)
objRoute.Calculate
Worksheets("Sheet1").Cells(13, 3) = objRoute.Distance

'If Worksheets("Sheet1").Cells(14, 1) = "" Then End Sub
szZip13 = Worksheets("Sheet1").Cells(14, 1)
objRoute.Waypoints.Add objMap.FindResults(szZip13).Item(1)
objRoute.Calculate
Worksheets("Sheet1").Cells(14, 3) = objRoute.Distance

'If Worksheets("Sheet1").Cells(15, 1) = "" Then End Sub
szZip14 = Worksheets("Sheet1").Cells(15, 1)
objRoute.Waypoints.Add objMap.FindResults(szZip14).Item(1)
objRoute.Calculate
Worksheets("Sheet1").Cells(15, 3) = objRoute.Distance

'If Worksheets("Sheet1").Cells(16, 1) = "" Then End Sub
szZip15 = Worksheets("Sheet1").Cells(16, 1)
objRoute.Waypoints.Add objMap.FindResults(szZip15).Item(1)
objRoute.Calculate
Worksheets("Sheet1").Cells(16, 3) = objRoute.Distance

'If Worksheets("Sheet1").Cells(17, 1) = "" Then End Sub
szZip16 = Worksheets("Sheet1").Cells(17, 1)
objRoute.Waypoints.Add objMap.FindResults(szZip16).Item(1)
objRoute.Calculate
Worksheets("Sheet1").Cells(17, 3) = objRoute.Distance

'If Worksheets("Sheet1").Cells(18, 1) = "" Then End Sub
szZip17 = Worksheets("Sheet1").Cells(18, 1)
objRoute.Waypoints.Add objMap.FindResults(szZip17).Item(1)
objRoute.Calculate
Worksheets("Sheet1").Cells(18, 3) = objRoute.Distance

End SubTIA

- co

VICTORY!

Using a loop, it works! Yay!
Thanks to all who helped out.

Posting the code here for your edification:
Private Sub CommandButton1_Click()
Dim Row As Integer

Set oApp = CreateObject("MapPoint.Application.NA.13")
oApp.Visible = True
Set objMap = oApp.NewMap
Set objRoute = objMap.ActiveRoute

szZip1 = Worksheets("Sheet1").Cells(2, 1)
objRoute.Waypoints.Add objMap.FindResults(szZip1).Item(1)

Row = 2
While Worksheets("Sheet1").Cells(Row, 1) <> ""
szZip = Worksheets("Sheet1").Cells(Row, 1)
objRoute.Waypoints.Add objMap.FindResults(szZip).Item(1)
objRoute.Calculate
Worksheets("Sheet1").Cells(Row, 3) = objRoute.Distance
Row = Row + 1
Wend

End Sub

- co