9.4. Using Data in MapPoint

If you have never used Microsoft MapPoint, you may be in for a pleasant surprise. MapPoint software creates regular maps, puts data on maps, draws territories, shows demographic information, provides driving directions, plans a route, and more. If you do not own MapPoint, at the time of this writing you can get a 60-day trial from Microsoft for under $15 (price depends on whether you want the US version, the Europe version, or both). In addition to ordering the trial CD, you can explore many VBA samples available on the Microsoft MapPoint web site that show how to perform automation of MapPoint. What I show here are two things that are both frequently requested by clients and very straightforward to do.

This first example shows how to create a map of territories, a very common request in sales organizations. You have many options available, such as Zip Code, three digit Zip Code, census tract, county, state, and metropolitan area. For this example, I use Zip Codes. I got a list of all of the Zip Codes for Philadelphia, PA. I then took the list of Zip Codes, which were in numerical order, and called the first 20 Territory A, the next 23 Territory B, and the rest Territory C. I saved this information in an Access database table.

Next, I went into MapPoint and went to Data Territories to use the Territory Wizard. When you go into the Territory Wizard, you see the screen in Figure 9-3. You can create the territories manually or from data. This example uses data in the Access database created from the Zip Code list.

Figure 9-3. The first screen in the MapPoint territory wizard

The next screen gives you the option of importing or linking to the data. Because the data is in a database and would likely be updated there if this were a real application, I choose to link to the data. If you import the data from here, it will be more difficult to update the data in the future. This screen is shown in Figure 9-4.

A file dialog comes up asking you to browse to the datafile that you want to use. You can choose from Excel, Access, Text Files, or Microsoft Data Link. Then it asks you to choose the table or query that you want to link to. After you choose, MapPoint takes a shot determining what fields hold the data that it needs. In this case, because I called the fields Territory and ZipCode, MapPoint makes the correct determination. Notice the drop-down boxes in Figure 9-5. If you want to change the selection that MapPoint assigned to the field, you can do so with the drop-down box.

When you move to the next screen, it asks you to choose a primary key for your data source. In this case, the Zip Code is the primary key. It imports the data and lets you know which Zip Codes (or whatever field you are using) could not be found. It then gives you a list of possible matches to use, where you can skip only the matching record or all records that could not be found by pressing the Skip All Records button. After it lists the possible matches, you see the map shown in Figure 9-6.

Figure 9-4. The second screen in the MapPoint territory wizard, which lets you choose to import or link to your data

Figure 9-5. The second screen in the MapPoint territory wizard, which lets you choose to import or link to your data

Notice on this map that the territories are not contiguous, which is the very reason that many people want to map data like this. Once this is done, you can take sales data by zip code, import or link to the data, and show it on the map.

To do the same thing from VBA, use Excel, Word, or Access. However, if you use an Access table and you try to do this from the same database, you get an error message that MapPoint cannot access the table. Look at Example 9-6. To use this example from any application, set a reference to the MapPoint Object Library.

Figure 9-6. An example of a territory map created from data in Access

Example 9-6. Excel automation of a MapPointTerritory map using Access data

 Public Sub mappointProg(  ) Dim mpApp As MapPoint.Application Dim mpMap As MapPoint.Map Dim mpRslt As MapPoint.FindResults Set mpApp = New MapPoint.Application Set mpMap = mpApp.NewMap mpApp.Visible = True Dim xlwb As Excel.Workbook Dim xlws As Excel.Worksheet Dim acFile As String Set xlwb = ActiveWorkbook Set xlws = xlwb.ActiveSheet acFile = "C:\BookInformation\Chapter9\Chapter9DBMappoint.MDB" mpMap.MapStyle = MapPoint.geoMapStyleData mpMap.DataSets.LinkTerritories _   acFile & "!tbl_Territories", PrimaryKeyField:="ZipCode", _   ImportFlags:=geoImportAccessTable Set mpRslt = mpMap.FindAddressResults(, "Philadelphia, PA") mpRslt.Item(1).Goto mpMap.CopyMap xlws.Paste mpMap.SaveAs "C:\BookInformation\Chapter9\temp.ptm" Set mpMap = mpApp.NewMap mpApp.Quit Set mpRslt = Nothing Set mpMap = Nothing Set mpApp = Nothing Set xlwb = Nothing Set xlws = Nothing Kill "C:\BookInformation\Chapter9\temp.ptm" End Sub

Automation of MapPoint is very similar to the other Office applications. First, set the application object to be equal to a new MapPoint application. While Excel has a workbook, Word has a document, and PowerPoint has a Presentation as the file, MapPoint has a map as its file or document. There is a NewMap method of the MapPoint application object used to set the reference to the map. Once that is done, become familiar with the MapPoint object model.

For this procedure, we use a data map (as opposed to a road and data map) by setting the MapStyle property of the map object. Next, there is a collection that holds all of the data for a given map called DataSets , which has a number of methods to bring in data, link to data, add demographics, or to add pushpins to the map. We use the method to link territories to an external data source. When you do this with the wizard in the MapPoint GUI, the map automatically zooms to the appropriate level so that you can see what you mapped. However, when you do it from VBA, you need to zoom the map yourself. Since I mapped Zip Codes in Philadelphia, all I had to do is find Philadelphia on the map. There is a collection called FindResults that holds all possible results of a search. Since I am looking for a very familiar place, I am confident that the first result will be the correct result. So, I tell the map to zoom to that location using the GoTo method of the first Item in the FindResults collection.

There is a CopyMap method of the Map object that places the graphic of the current map on the clipboard. Use the Paste method of any Office application to paste it.

Other Techniques

Sometimes you'll want to move data from other applications into Office. There are a lot of ways that this can be accomplished: text file export, DDE, copy and paste, etc. I have built several applications that use DDE to bring in the data from a field in a different application. Normally, those decisions are driven from the other application. For example, if you have a Sales Force Automation (SFA) application, and you want to send a letter to certain customers that you speak with, you can set up a Word document to pull data from particular fields to fill in a letter anytime that it is opened. In most cases, DDE will be a very easy way to do this. There are many examples on how to do this with various applications on the Internet.

Another way to accomplish these tasks is if the other application has an Application Programming Interface (API) . Depending on the application, you might need an additional license to use the API or have to sign up as a developer with the software publisher. As a general rule, using an API gives you more control over the application than DDE.

DDE and application-specific APIs are outside the scope of this book, but if you need to interact with another application, it is certainly something to consider. All of this can be done from within VBA. If you are in Excel, you can easily get data via DDE into a single cell using a formula. The formula is =Application|File Name!FieldName, so if you want to get data from a form field in Word named Text1, and the document is called Chapter9DDEDocument.DOC, type the following formula into Excel. When you try this, have the Word document open, or you have to go through a warning and Excel opens the Word document anyway.

     =WinWord|'C:\Chapter9\Chapter9DDEDocument.doc'!Text1

This formula can also be used with other applications that allow you to communicate via DDE. Notice that you should use single quotes for the file name. For some applications, you use a topic name instead of a document name; this is common in SFA applications where you are not opening up documents, but rather structured customer records. In cases like this, there are often topics like Contact, Billing, Sales, etc. If you do this type of automation, I strongly suggest that you go to the vendor's documentation to ensure that you are pulling in the right information.

The final MapPoint example, shown in Example 9-7, shows how to get directions between two points in MapPoint. With this example, you could pass the locations as parameters and make a more general-purpose procedure. The example shown calculates the directions from Philadelphia, PA to Baltimore, MD, and then pastes the directions to an Excel worksheet.

Example 9-7. Procedure to calculate the directions between locations

 Public Sub mappointProg2(  ) Dim mpApp As MapPoint.Application Dim mpMap As MapPoint.Map Dim mpDir As MapPoint.Route Set mpApp = New MapPoint.Application Set mpMap = mpApp.NewMap mpApp.Visible = True Set mpDir = mpMap.ActiveRoute Dim xlwb As Excel.Workbook Dim xlws As Excel.Worksheet Dim acFile As String Set xlwb = ActiveWorkbook Set xlws = xlwb.Sheets("Sheet2") xlws.Activate mpMap.MapStyle = MapPoint.geoMapStyleRoad mpDir.Waypoints.Add mpMap.FindAddressResults(, "Philadelphia, PA").Item(1) mpDir.Waypoints.Add mpMap.FindAddressResults(, "Baltimore, MD").Item(1) mpDir.Calculate While mpDir.IsCalculated = False   Debug.Print "Waiting" Wend mpDir.Directions.Location.Goto mpMap.CopyDirections mpMap.SaveAs "C:\BookInformation\Chapter9\temp.ptm" Set mpMap = mpApp.NewMap mpApp.Quit xlws.Paste Set mpDir = Nothing Set mpMap = Nothing Set mpApp = Nothing Set xlwb = Nothing Set xlws = Nothing Kill "C:\BookInformation\Chapter9\temp.ptm" End Sub

The new object used in this procedure is the Route object, which also includes a Waypoints collection that holds the stops on the route. Once you have set the waypoints, calculate the route. Because you cannot access the Directions collection of the route until it has been calculated, I put in a while loop that makes sure the route is calculated before accessing the Directions collection. If the route has not finished calculating yet, it prints Waiting in the Immediate Window in Excel. In the previous example, we used the CopyMap method to copy the map to the clipboard. In this example, we use the CopyDirections method to copy the directions to the clipboard.

As you might imagine, there are a lot of uses for this. For example, you could send customers directions to your company personalized for their actual address. You might also want to show a map, and you could easily use the CopyMap method to do so. It is probably more likely that you would use Word for these applications of MapPoint and the code would be very similar.