MapPoint Forums

MapForums

Community of VE/MapPoint Users and Developers




Trying to integrate Excel data into MapPoint

This is a discussion on Trying to integrate Excel data into MapPoint within the MapPoint 2006/2009 Discussion forums, part of the Map Forums category; OK... I'm new to the whole data integration thing when it comes to MapPoint and Excel. I work for ...


Go Back   MapPoint Forums > Map Forums > MapPoint 2006/2009 Discussion

Trying to integrate Excel data into MapPoint Trying to integrate Excel data into MapPoint

Register Blogs FAQ Members List Calendar Search Today's Posts Mark Forums Read

Notices

Notice: Now taking orders for MapPoint 2009!


Click here to register

Reply
Page 1 of 2 1 2 >

 

LinkBack (2) Thread Tools Display Modes
  2 links from elsewhere to this Post. Click to view. #1 (permalink)  
Old 04-17-2008
noob
White Belt
  
Join Date: Apr 2008
Location: Portland, Oregon
Posts: 9
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?

Any helpful input would be appreciated.

- co
Digg this Post!Add Post to del.icio.usBookmark Post in TechnoratiFurl this Post!Spurl this Post!Reddit! Wong this Post!
Reply With Quote
  #2 (permalink)  
Old 04-17-2008
noob
White Belt
  
Join Date: Apr 2008
Location: Portland, Oregon
Posts: 9
Re: Trying to integrate Excel data into MapPoint
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.
Digg this Post!Add Post to del.icio.usBookmark Post in TechnoratiFurl this Post!Spurl this Post!Reddit! Wong this Post!
Reply With Quote
  #3 (permalink)  
Old 04-17-2008
Eric Frost's Avatar
Administrator
Black Belt
  
Join Date: Jul 1992
Posts: 2,210
Blog Entries: 1
Re: Trying to integrate Excel data into MapPoint
Have you seen the Excel / MapPoint examples at

Working With Excel and MapPoint - MP2K Magazine ?

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
__________________
Order MapPoint 2006 | Read Programming MapPoint in .NET | Start Using the Pushpin Tool - Free Trial Download - click here.
Digg this Post!Add Post to del.icio.usBookmark Post in TechnoratiFurl this Post!Spurl this Post!Reddit! Wong this Post!
Reply With Quote
  #4 (permalink)  
Old 04-17-2008
Junior Member
White Belt
  
Join Date: Apr 2008
Posts: 1
Re: Trying to integrate Excel data into MapPoint
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.
Digg this Post!Add Post to del.icio.usBookmark Post in TechnoratiFurl this Post!Spurl this Post!Reddit! Wong this Post!
Reply With Quote
  #5 (permalink)  
Old 04-17-2008
noob
White Belt
  
Join Date: Apr 2008
Location: Portland, Oregon
Posts: 9
Re: Trying to integrate Excel data into MapPoint
Quote:
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
Digg this Post!Add Post to del.icio.usBookmark Post in TechnoratiFurl this Post!Spurl this Post!Reddit! Wong this Post!
Reply With Quote
  #6 (permalink)  
Old 04-17-2008
noob
White Belt
  
Join Date: Apr 2008
Location: Portland, Oregon
Posts: 9
Re: Trying to integrate Excel data into MapPoint
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:
Code:
    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.
Last edited by checkedout; 04-17-2008 at 05:18 PM.
Digg this Post!Add Post to del.icio.usBookmark Post in TechnoratiFurl this Post!Spurl this Post!Reddit! Wong this Post!
Reply With Quote
  #7 (permalink)  
Old 04-17-2008
Eric Frost's Avatar
Administrator
Black Belt
  
Join Date: Jul 1992
Posts: 2,210
Blog Entries: 1
Re: Trying to integrate Excel data into MapPoint
Looking forward to your next vent, maybe we can aid you along... or not . but don't hesitate to post details anyway.. someone else searching could also learn from your journey.

Eric
__________________
Order MapPoint 2006 | Read Programming MapPoint in .NET | Start Using the Pushpin Tool - Free Trial Download - click here.
Digg this Post!Add Post to del.icio.usBookmark Post in TechnoratiFurl this Post!Spurl this Post!Reddit! Wong this Post!
Reply With Quote
  #8 (permalink)  
Old 04-17-2008
noob
White Belt
  
Join Date: Apr 2008
Location: Portland, Oregon
Posts: 9
Re: Trying to integrate Excel data into MapPoint
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.
Code:
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 Sub
Obviously, 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
Digg this Post!Add Post to del.icio.usBookmark Post in TechnoratiFurl this Post!Spurl this Post!Reddit! Wong this Post!
Reply With Quote
  #9 (permalink)  
Old 04-17-2008
Eric Frost's Avatar
Administrator
Black Belt
  
Join Date: Jul 1992
Posts: 2,210
Blog Entries: 1
Re: Trying to integrate Excel data into MapPoint
I don't think so, but

Thanks!

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

Eric
__________________
Order MapPoint 2006 | Read Programming MapPoint in .NET | Start Using the Pushpin Tool - Free Trial Download - click here.
Digg this Post!Add Post to del.icio.usBookmark Post in TechnoratiFurl this Post!Spurl this Post!Reddit! Wong this Post!
Reply With Quote
  #10 (permalink)  
Old 04-17-2008
noob
White Belt
  
Join Date: Apr 2008
Location: Portland, Oregon
Posts: 9
Re: Trying to integrate Excel data into MapPoint
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
Digg this Post!Add Post to del.icio.usBookmark Post in TechnoratiFurl this Post!Spurl this Post!Reddit! Wong this Post!
Reply With Quote
Reply
Page 1 of 2 1 2 >

« Location & FindResults not always returning City/State/etc.? | How to detect a particular pushpin »

LinkBacks (?)
LinkBack to this Thread: http://www.mapforums.com/trying-integrate-excel-data-into-mappoint-7511.html
Posted By For Type Date
MAPPOINT DOWNLOADS: License Agreement - MP2K Magazine This thread Refback 04-21-2008 06:33 PM
MapPoint Help - MP2K Magazine This thread Refback 04-18-2008 03:49 AM

Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)

 
Thread Tools
Display Modes
Linear Mode Linear Mode

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads

Thread Thread Starter Forum Replies Last Post
Problems exporting MapPoint data to Excel in embedded map o0beaner MapPoint 2006/2009 Discussion 4 03-12-2007 02:14 PM
How do I Integrate MapPoint with MSAccess newlifcc MapPoint 2006/2009 Discussion 1 09-21-2005 12:22 PM
Linking Data within an Excel Workbook to Mappoint XLR Excel MapPoint 2006/2009 Discussion 0 07-06-2005 01:23 PM
Error on import data from Excel to mapPoint Anonymous MapPoint 2006/2009 Discussion 5 01-27-2005 03:06 PM
importing data from excel to mappoint Layne MapPoint 2006/2009 Discussion 0 05-09-2003 12:40 PM


All times are GMT -5. The time now is 10:13 PM.

Marketing Strategy - VSAT - Used Cars - Book a Hotel - Moving Services - Archive - Top

Powered by vBulletin® Version 3.7.1
Copyright ©2000 - 2008, Jelsoft Enterprises Ltd.
Search Engine Friendly URLs by vBSEO 3.2.0 RC5
MP2K Magazine
Visitor Map

Exeter Flights
Visit Holiday Hypermarket online to find all the essential information about Exeter Airport and Exeter flights. Booking is quick, easy and cheap with Holiday Hypermarket.

Best Travel Agent
Book your Travel with the UK's best Travel Agent - as named at the Guardian Unlimited Travel awards.

Portugal Holiday
For such a small country, you have many options from which to choose. A Portugal Holiday can include fantastic beaches and stunning scenery. Check out our great deals.

Holidays to Cuba
For the best offers on holidays to Cuba, visit The Holiday Place today. Find a deal to suit you and your budget online!

Cheap Egypt Holidays
Pick up a bargain cheap Egypt holiday online when you visit ulookubook.com. Just check out our tips to make sure you book at the right time to get a great holiday for a great price. Finding cheap Egypt holidays can be simple when you know how.

Compare Holidays
Compare holidays online where you can see all the amazing possibilities at Travel.co.uk

Cheap Holidays to Lanzarote
Visit the Canary Islands, even if you're cash strapped! View cheap holidays to Lanzarote at On The Beach!


1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47