Click here to view this issue online
Click here!
 Tuesday - April 26, 2005
Special Edition - Excel / MapPoint Tutorial 
MapPoint ExcelDeveloping 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.

Previous issues are available in the newsletter archive.

Copyright 1999-2005 MP2K Magazine
Click here!