< Day Day Up > |
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 wizardThe 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 dataFigure 9-5. The second screen in the MapPoint territory wizard, which lets you choose to import or link to your dataNotice 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 AccessExample 9-6. Excel automation of a MapPointTerritory map using Access dataPublic 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.
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 locationsPublic 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. |
< Day Day Up > |