MapPoint Get MSAs Add-in
Microsoft MapPoint 2002 and later
Summary: Learn how to create a MapPoint add-in by using the MapPoint object model and Microsoft Visual Basic .NET. With the add-in, users can query Pushpin sets to find out if any of the Pushpins are located within Metropolitan Statistical Areas (MSAs). The add-in then displays the query results in a Microsoft Excel workbook. (12 printed pages)
Click here to download the code sample for this article.
A Metropolitan Statistical Area (MSA) is a geographical area, defined by the federal Office of Management and Budget (OMB), that represents the metropolitan area of a city. In most of the United States, an MSA consists of a county or a group of counties.
MSA information can be useful in business planning, and the Get MSAs add-in provides an easy way for MapPoint users to extract and use the MSA information that is built into MapPoint. Suppose you have a list of 20,000 potential customers and want to figure out which ones to target for a sales campaign. You can import your customer list into MapPoint, and then use the Get MSAs add-in to query your Pushpin set and create a list of customers that are located within MSAs. You can then target your sales efforts to customers located in specific MSAs, based on demographic data provided by MapPoint.
The Get MSAs add-in was written in Microsoft Visual Basic .NET using the MapPoint object model and the Visual Studio Add-in Wizard. The add-in adds a dialog box in MapPoint in which you select the Pushpin set that you want to query. When you click OK, the add-in uses the MapPoint ObjectsFromPoint method to check each Pushpin to determine if it has a location type of MSA. If it does, the name of the Pushpin and the name of the MSA are exported to an Excel workbook. When the search is complete, a dialog box displays how many Pushpins were queried and how many are in MSAs, and the list of results is displayed in an open Excel workbook.
This article first describes how to install and run the completed add-in, which is provided. The article then walks you through the steps necessary to create a MapPoint add-in and use the ObjectsFromPoint method. Although the Get MSAs add-in uses the ObjectsFromPoint method to determine whether a location type of MSA is associated with each Pushpin, you can use the ObjectsFromPoint method to return any of the location information objects available in MapPoint, such as city, state, ZIP Code, or country.
This article assumes that you are familiar with Visual Basic .NET and MapPoint. For more information about programming with the MapPoint object model, see About Programming with MapPoint. For more information about MSAs and other types of standard geographical divisions that MapPoint supports, see Using Standard Geographic Codes to Import Data into MapPoint.
To run the add-in or use the sample code, you must have the following software installed on your computer:
- Microsoft .NET Framework 1.1
- Visual Studio .NET 2003
- MapPoint 2002 or later
- Excel 2002 or later
To install the add-in
- Download the add-in from the Microsoft Download Center.
- Double-click the file MapPointGetMSAs.sln to open it in Visual Studio.
- On the Project menu, click Build Solution.
- In Solution Explorer, right-click the project GetMSAs (the Setup project), and then click Install. The Get MSAs Installation Wizard starts.
- Follow the instructions in the wizard, and then click Finish.
Note You can uninstall the add-in by using Add or Remove Programs in Control Panel.
To run the add-in
- In MapPoint, on the Tools menu, click Get MSA Information for a Pushpin set.
- In the box that appears, select the Pushpin set that you want to query, and then click Get MSAs.
This section is divided into two parts: the first part describes how to create the add-in in Visual Studio .NET. The second part describes how to use the ObjectsFromPoint method to get MSA information from a Pushpin set and send it to Excel.
Creating a MapPoint Add-in
Creating the add-in consists of three main steps:
- Create the basic framework for the add-in with the Visual Studio Add-in Wizard.
- Add code to create a menu item for the add-in in MapPoint and perform some clean-up work after the add-in runs. You add this code to a file generated by the Add-in Wizard.
- Create the user interface for the add-in and add code that provides its core functionality.
To create the framework for the add-in
- In Visual Studio, on the File menu, point to New, and then click Project.
- Under Project Types, expand Other Projects, expand Extensibility Projects, and then under Templates, click Shared Add-In.
- Give the project and solution a name and, optionally, specify a new location for the project files, and then click OK. The Add-in Wizard starts.
- On the Welcome page, click Next.
- Work through the pages in the wizard, choosing the following options:
- On the Select a Programming Language page, select Visual Basic .NET as your programming language.
- On the Select an Application Host page, clear all check boxes except Microsoft MapPoint.
- On the Name and Description page, type a name and description for the add-in.
- On the Choose Add-in Options page, select the I would like my add-in to load when the host application loads check box.
- On the summary page, review the settings you've chosen, and then click Finish.
To add a menu item and clean-up code
The Add-in wizard creates the file Connect.vb to implement the methods of the IDTExtensibility2 interface that are necessary for an add-in. The Connect class in Connect.vb contains code to expose the add-in to the end user and clean up after the application runs.
- In Solution Explorer, double-click the file Connect.vb to open it, and after the line containing the GuidAttribute element, replace the existing code with the following code:
Public Class Connect Implements Extensibility.IDTExtensibility2 Dim applicationObject As Object Dim addInInstance As Object Protected Friend Shared MPApp As MapPoint.Application Public Sub OnBeginShutdown(ByRef custom As System.Array) Implements Extensibility.IDTExtensibility2.OnBeginShutdown End Sub Public Sub OnAddInsUpdate(ByRef custom As System.Array) Implements Extensibility.IDTExtensibility2.OnAddInsUpdate End Sub Public Sub OnStartupComplete(ByRef custom As System.Array) Implements Extensibility.IDTExtensibility2.OnStartupComplete End Sub Private Sub ReleaseObject(ByVal MPApp As Object) Try System.Runtime.InteropServices.Marshal.ReleaseComObject(MPApp) Catch Finally MPApp = Nothing End Try End Sub Public Sub OnDisconnection(ByVal RemoveMode As Extensibility.ext_DisconnectMode, ByRef custom As System.Array) Implements Extensibility.IDTExtensibility2.OnDisconnection On Error Resume Next If RemoveMode <> Extensibility.ext_DisconnectMode.ext_dm_HostShutdown Then _ Call OnBeginShutdown(custom) ReleaseObject(MPApp) End Sub Public Sub OnConnection(ByVal application As Object, ByVal connectMode As Extensibility.ext_ConnectMode, ByVal addInInst As Object, ByRef custom As System.Array) Implements Extensibility.IDTExtensibility2.OnConnection applicationObject = application addInInstance = addInInst MPApp = CType(application, MapPoint.Application) ' Add an item to the Tools menu in MapPoint. MPApp.AddCommand("Get &MSA information for a Pushpin set...", "ShowAddInForm", Me) End Sub Public Sub ShowAddInForm() Dim FormAddIn As New MapPointGetMSAs.FormMSAQuery FormAddIn.ShowDialog() End Sub End Class
To add the user interface and core functionality
- Add a new form to the project, and name it FormMSAQuery. This form is loaded by the ShowAddInForm subroutine, which you added to Connect.vb earlier.
- Add a button and combo box to the form, and name them ButtonCreateList and ComboBoxDataSets.
- Copy the sample code from the following section into the code view for the new form.
- Add references to the COM objects Microsoft MapPoint <version> and Microsoft Excel <version>:
- On the Project menu, click Add Reference, and then click the COM tab.
- Under Component Name, click the name of each component you're adding while pressing CTRL, click Select, and then click OK.
- Build the solution, and then run Setup, using the procedure described in Installing and Running the Add-in.
When you launch MapPoint, your add-in will be ready to go on the Tools menu.
Using the ObjectsFromPoint Method
The ObjectsFromPoint method queries the x and y coordinates of a Pushpin and returns all location types that are associated with that point on the map; latitude and longitude, city, country, county, ZIP Code, MSA, and so on. If the Pushpin is not in an MSA, that location type, whose value is 10, is not returned by the query. For more information about MapPoint location types, see the MapPoint Help topic GeoShowDataBy values.
The example code in this section is divided into four main steps:
- Get the MapPoint Application object and data sets, and find out which Pushpin set the user wants to query.
- Set the zoom level and map style so that we can search for MSAs.
- Call ObjectsFromPoint, set up Excel, and write the query results to the workbook.
- Return the zoom level and map style to their original settings and display the results of the search.
Getting the MapPoint Application Object and Pushpin Sets
We'll begin by declaring the variables we'll need. Next, we'll load the form, getting the active MapPoint Application object and map. We'll then populate the combo box on the form with the Pushpin sets on the active map. Finally, we'll use the Click event of the ButtonCreateList button to get the Pushpin set the user chose from the combo box.
'Declare variables. Dim MPApp As MapPoint.Application Dim Map As MapPoint.Map Dim Pins As MapPoint.DataSet Dim Pin As MapPoint.Pushpin Dim FindResults As MapPoint.FindResults Dim Result As Object Dim DSs As MapPoint.DataSets Dim DS As MapPoint.DataSet Dim Center As MapPoint.Location Dim Style As MapPoint.GeoMapStyle Dim ExcelApp As New Excel.Application Dim ExcelSheet As Excel.Worksheet Dim Row, Alt, n As Integer Dim x, y As Long Dim MSA, City, PinName, SetName As String Private Sub FormGetMSAs_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load 'Get the active application, map, and data sets. MPApp = GetObject(, "MapPoint.Application") MPApp.UserControl = True Map = MPApp.ActiveMap DSs = MPApp.ActiveMap.DataSets 'Position the add-in form on the screen. Me.Left = MPApp.Left + 50 Me.Top = MPApp.Top + 50 'Populate the drop-down list with the names of the Pushpin sets on the 'current map. 'If none of the data sets are Pushpin sets or if there are no 'data sets, inform the user. If DSs.Count <> 0 Then For Each DS In DSs If DS.DataMapType = MapPoint.GeoDataMapType.geoDataMapTypePushpin Then ComboBoxDataSets.Items.Add(DS.Name) Else End If If ComboBoxDataSets.Items.Count = 0 Then MsgBox("None of the data sets on the map are Pushpin sets. Convert the data set that you want to query to a Pushpin set (non-aggregated or point data), and then run the Get MSAs tool again.", MsgBoxStyle.ApplicationModal, "Get MSA Information") Me.Close() End If Next 'If no data sets exist, display a message. Else MsgBox("There are no data sets on this map. Please import your data and try again. ", MsgBoxStyle.ApplicationModal, "Get MSA Information") MPApp = Nothing Me.Close() End If End Sub Private Sub ButtonCreateList_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCreateList.Click 'On button click, if the user selected a Pushpin set in the drop-down 'list, use it. If not, prompt the user again. Try If ComboBoxDataSets.Text <> "Select a pushpin set" Then SetName = ComboBoxDataSets.Text Pins = Map.DataSets.Item(SetName) CreateList() Else MsgBox("Please select a Pushpin set.", MsgBoxStyle.Exclamation, "Get MSA Information") End If Catch ex As Exception MsgBox(ex.ToString & " on record number " & n - 1, MsgBoxStyle.Exclamation, "Error getting data") Finally End Try End Sub
Setting the Zoom Level and Map Style
Different location types are displayed at different zoom levels of the map. For example, if you view a map at a high zoom level, less detail is displayed. As you zoom in, you see more city names, parks, roads, and so on.
MSAs are consistently available at a zoom level that is equal to an altitude of 15 miles and when the map style is set to Data Map. To be able to retrieve the data we're looking for, we'll set the map to that zoom level and style before calling the ObjectsFromPoint method. First, however, we'll determine the current map style, altitude, and location so that we can change the settings back later.
Note Remove the comment character from the MPApp.Activate statement in the following code if you want to bring MapPoint to the foreground to watch it search.
Private Sub CreateList() Center = Map.Location Style = Map.MapStyle Alt = Map.Altitude 'MPApp.Activate() 'Set the map to the style and altitude where MSAs are displayed. Map.MapStyle = MapPoint.GeoMapStyle.geoMapStyleData Map.Altitude = "15" On Error GoTo ErrorHandler
Calling ObjectsFromPoint and Writing Data to Excel
Next, we'll iterate through the Pushpin set, calling ObjectsFromPoint for each Pushpin, looking for object type 10. If a Pushpin doesn't have an object type 10 at this zoom level, it's not in an MSA. If the Pushpin is in an MSA, we'll send its name and the MSA it's in to the Excel workbook.
If a user-defined Data Map (shaded areas, charts, sized circles, and so on) exists in the current map in addition to Pushpin sets, a DataMap object is returned as an object of a Pushpin if the DataMap object and Pushpin touch. But because a DataMap object does not have a location type, it will throw an error. We'll skip DataMap objects by using the Case Nothing statement in the following code.
'Keep track of the what is being sent to Excel. Row = 1 Dim RS As MapPoint.Recordset = Pins.QueryAllRecords RS.MoveFirst() Do Until RS.EOF n += 1 RS.Pushpin.Location.GoTo() RS.Pushpin.Select() FindResults = Map.ObjectsFromPoint(Map.LocationToX _ (RS.Pushpin.Location), Map.LocationToY(RS.Pushpin.Location)) 'ObjectsFromPoint gets all the location objects for these x and y 'coordinates of the Pushpin location. For Each Result In FindResults Select Case Result.type Case Nothing Exit Select Case Is = 10 If Row = 1 Then 'Set up the Excel worksheet in order to enter data. ExcelApp = New Excel.Application ExcelApp.Workbooks.Add() ExcelSheet = ExcelApp.Worksheets.Item(1) ExcelApp.Visible = True ExcelApp.UserControl = True End If Row += 1 ExcelSheet.Cells(Row, 1).value = _ RS.Pushpin.Name ExcelSheet.Cells(Row, 2).value = Result.name End Select Next RS.MoveNext() Loop 'Add header information to the Excel worksheet if records exist. If Row > 1 Then ExcelSheet.Cells(1, 1).value = "Name" ExcelSheet.Cells(1, 2).value = "Metro Area" End If ErrorHandler: 'MsgBox(Err.Description & " on record number " & n - 1,_ MsgBoxStyle.Exclamation, "Error Getting Data") Resume Next Me.Close()
Resetting the Map Style and Zoom Level and Displaying Results
Our last steps are to set the user's map back to the original style and zoom level and indicate how many Pushpins were in MSAs.
MPApp.Activate() MPApp.ActiveMap.MapStyle = Style MPApp.ActiveMap.Altitude = Alt Center.GoTo() Select Case n Case 0 MsgBox("No Pushpins were found in this data set. ", MsgBoxStyle.Information, "Process Complete") Case 1 Select Case Row Case 1 MsgBox(n & " Pushpin was checked, it is not within an MSA bounds. ", MsgBoxStyle.Information, "Process Complete") Case 2 MsgBox(n & " Pushpin checked, it is in an MSA. ", MsgBoxStyle.Information, "Process Complete") End Select Case Else Select Case Row Case 1 MsgBox(n & " Pushpins checked, none were in MSAs. ", MsgBoxStyle.Information, "Process Complete") Case 2 MsgBox(n & " Pushpins checked, 1 was in an MSA. ", MsgBoxStyle.Information, "Process Complete") Case Else MsgBox(n & " Pushpins checked, " & Row - 1 & " were in MSAs. ", MsgBoxStyle.Information, "Process Complete") End Select End Select End Sub End Class
With the Get MSA information add-in, you can use the MSA information provided by MapPoint for business planning and analysis. Although this add-in uses the ObjectsFromPoint method to search for the location type MSA for each Pushpin, you can modify the add-in to return any of the location information objects available in MapPoint, such as city, state, ZIP Code, or country. Remember that all location types are not displayed at all levels of a MapPoint map, so make sure that the map is at a zoom level where the location type you want is displayed before calling ObjectsFromPoint. For more information about MapPoint location objects that you can access through the ObjectsFromPoint method, see the MapPoint Help topic, GeoShowDataBy values. For more information about programming with MapPoint, visit the MapPoint Developer Center on MSDN.
Laura Cramb is a software test engineer in the MapPoint Product Group.