Here is one way to do it. I open a text file in excel, pull the fields into an array and then add each one as a way point. This way I have control over each address in case it is a bad address. My start & end are always the same, so that is set up in separate arrays.
Code:
' note: arrays Route_Start, Route_End and AddrInfo are already populated
AddWaypoint Route_Start(1), Route_Start(2), Route_Start(3), Route_Start(4), Route_Start(5)
'''''''''''''''''''''''''''''''''''''''''''''''''''''''
'need to skip first header column, start at 2
For rowIndex = 2 To nrow
AddWaypoint AddrInfo(rowIndex, 1), AddrInfo(rowIndex, 2), _
AddrInfo(rowIndex, 3), AddrInfo(rowIndex, 4), _
AddrInfo(rowIndex, 5)
Label_total.Caption = rowIndex
Next rowIndex
AddWaypoint Route_End(1), Route_End(2), Route_End(3), Route_End(4), Route_End(5)
Set objDataSet = MPApp.ActiveMap.DataSets("My Pushpins")
'Retrieve all records
Set objRecordset = objDataSet.QueryAllRecords
objDataSet.ZoomTo
' optimize & get directions:
MPMap.ActiveRoute.Waypoints.Optimize
MPMap.ActiveRoute.Calculate
Code:
Private Sub AddWaypoint(Name As String, Street As String, _
City As String, State As String, Zip As String)
Dim l As Location
Dim p As Pushpin
Dim w As Waypoint
Dim bad_address As Integer
' Find the address on the map.
Set l = MPMap.FindAddress(Street, City, State, Zip)
If l Is Nothing Then
bad_address = 1
'If Check_debug.Value = 1 Then
MsgBox "BAD ADDRESS: " & Street & " " & City & " " & Zip
'End If
nBadAddr = nBadAddr + 1
BadAddrInfo(nBadAddr, 1) = Name
BadAddrInfo(nBadAddr, 2) = Street
BadAddrInfo(nBadAddr, 3) = City
BadAddrInfo(nBadAddr, 4) = State
BadAddrInfo(nBadAddr, 5) = Zip
End If
If YaddPushpins = 1 And bad_address = 0 Then
Set p = MPMap.AddPushpin(l, Name)
MPMap.ActiveRoute.Waypoints.Add p, Name & " " & Street
End If
' Destroy the objects before returning.
Set l = Nothing
Set p = Nothing
Set w = Nothing
End Sub
Hope this is helpful.
12-14-2003
Linear Mode
