PDA

View Full Version : MapPoint beginner- in need of direction & guidance using excel bath process!



aperez
12-23-2011, 01:12 PM
I’ve been able to setup excel to retrieve route distance using two sets of zip codes (origin & destination). I have over 25,000 records, so doing this as a batch process. Everything works fine, but every time there is a zip code from either origin or destination that is not found in MapPoint, I receive a Visual Basic Error: “The requested member of the collection does not exist. Use a valid name or index number.” Here’s the code I’ve been using as it loops to next set of rows, but hangs when it runs into an unknown zip code. How can I just flag that record as unknow, but let system continue batch process without hanging up?
------------------------------------------------------------------------------------------
Dim oApp As MapPoint.Application

Private Sub CommandButton1_Click()
Set oApp = CreateObject("MapPoint.Application.NA.17")
oApp.Visible = True
Set objMap = oApp.NewMap
Set objRoute = objMap.ActiveRoute

szZip1 = Worksheets("Sheet1").Cells(2, 1)
szZip2 = Worksheets("Sheet1").Cells(2, 2)
nRow = 2

Do
szZip2 = Worksheets("Sheet1").Cells(nRow, 2)
'Add route stops and calculate the route
objRoute.Waypoints.Add objMap.FindResults(szZip1).Item(1)
objRoute.Waypoints.Add objMap.FindResults(szZip2).Item(1)
objRoute.Calculate
Worksheets("Sheet1").Cells(nRow, 3) = objRoute.Distance
objRoute.Clear
nRow = nRow + 1
Loop While Worksheets("Sheet1").Cells(nRow, 1) <> ""
End Sub
---------------------------------------------------------------

Eric Frost
12-24-2011, 08:41 AM
Put a line in at the top like

On Error Resume Next

Then it will just skip the zip codes it can't find.

let us know how it works!

Eric

Eric Frost
12-24-2011, 08:59 AM
I should say put it AFTER this line so it is in the subroutine

Private Sub CommandButton1_Click()

Eric

aperez
12-24-2011, 10:03 AM
Eric, it works perfectly! I placed right after the subrouting line as noted above. Thank you very much for taking the time in helping me out with this. Once again, thank you Eric.

Eric Frost
12-24-2011, 11:46 AM
Sure! Yeah I think I recognize the code as mine so I might as well help to finish for you :-) Where did you get it, was it from another thread or download?

Eric

aperez
12-24-2011, 01:37 PM
Yes...that's correct! I belive it is yours 'Eric Frost' correct. I started reading up on this link and started to gatehr info from this point... What's New at MP2K Magazine - The Magazine for MapPoint (http://www.mp2kmag.com/update/mappoint.newsletter/2004-09-30/#features_sec)

Once again, thank you. I just finishied running remaing 3,000 of 25,000 records...worked like a charm.

Eric Frost
12-26-2011, 05:21 AM
Great! Glad to hear