Thread: Trying to integrate Excel data into MapPoint
View Single Post

  #8 (permalink)  
Old 04-17-2008
checkedout checkedout is offline
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
Reply With Quote