Developing
With
MapPoint and Excel, Part III - Distance Between Zip Codes
Welcome to a special abbreviated issue of the MP2Kmag newsletter. In
the last issue we had promised to publish another installment of the
MapPoint / Excel tutorial we started some months back before the next
full newsletter was published so here it is. Look forward to the fourth
installment to be published shortly in an upcoming newsletter.
In considering where to go with the code we started to lay out
in the last two issues, a
recent
post in the forum helped me
decide to look at routing.
In the last tutorial we showed how you could go about
Mapping
an Address that is stored
in Excel. In this tutorial we will show how to calculate a
route from one zip code to another and put the distance back out in
Excel.
First, download this spreadsheet -
Manhattan_to_Chicago.zip.
As usual we are going to keep it short and sweet. If you want to
set up loops to calculate in batch or set up more complicated
routes and are having trouble with it, feel free to discuss it in the
forum
area for this mini-tutorial.
In the last tutorial we had mapped a complete address using
FindAddressResults. In this example we use a different geocoding
method, simply FindResults which will try to determine what you are
trying to map. It accepts addresses, place names, business names,
countries, zip codes, etc. While not as specific as FindAddressResults
and the results may be more ambiguous, it is more versatile and allows
you to replace the contents of the A2 and B2 cells in the spreadsheet
above with just about anything.
Here is a discussion from the MapPoint help file regarding when to use
FindAddressResults, FindResults, or a third method FindPlaceResults:
"If
the input is a place name that MapPoint might interpret as an
address (for example "Club Easy Street"), the results will be
ambiguous. Therefore, if you are not sure whether the input is a place
name or an address, it is a good idea to use multiple
methods—the FindResults,
FindAddressResults,
and FindPlaceResults
methods—and then compare the results."
Note that all of these methods return an array of Locations with the
most likely match at the top, thus the .Item(1) at the end of the
statement. The Location found by FindResults is passed to the
Waypoints.Add method of the Route object. The first one added just
defaults to being the starting location and the second one is
destination. The calculate method then sets up the results of the Route
object which allows us to read the distance and write it back to Excel.
Here is the complete code.
Dim
oApp As MapPoint.Application
Private Sub CommandButton1_Click()
Set oApp = CreateObject("MapPoint.Application.NA.11")
oApp.Visible = True
Set objMap = oApp.NewMap
Set objRoute = objMap.ActiveRoute
szZip1 = Worksheets("Sheet1").Cells(2, 1)
szZip2 = Worksheets("Sheet1").Cells(2, 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(2, 3) = objRoute.Distance
End Sub
Is that pretty clear? If not, or again if you have any trouble, take a
look back at
Part
I &
Part
II and also please post in
the
forum
area for these tutorials.
Finally, have fun!
Keep an eye out for
Part IV
which should be included in an upcoming issue of the
MP2Kmag
Newsletter. To make sure you are
subscribed, send an e-mail to newsletter-subscribe@mp2kmag.com.