This is a discussion on Sample Excel Application - Automating MapPoint and Pasting Map Images into Excel within the Lounge v.2.0 forums, part of the The Lounge category; This articles shows a relatively simply way to control MapPoint and paste map images into Excel based on some event ...
This articles shows a relatively simply way to control MapPoint and paste map images into Excel based on some event or changes in Excel, bringing powerful mapping capability into Microsoft Excel.
MapPoint and Excel work well together.
I would posit that more than 50% of the work done with MapPoint also involves Excel in some capacity. To some degree, this is by necessity and design. MapPoint does not have it's own, built-in Table Viewer and Editor such as other mapping software like MapInfo Pro and ArcGIS.
Also, MapPoint does not have it's own VBA scripting editor, thus necessitating anything else to make the COM calls. This could be as simple as interpreted Python scripts, or the latest .NET. In any case, that job (automating MapPoint) often falls to Excel's macro/VBA facility.
This article came about in response to a request for help with MapPoint in the forum. Originally seeking to automate a MapPoint map embedded into Excel, we decided this would not be the ideal solution as the image that results in Excel and the embedded document is closed, often does not look good.
I do think automation of an embedded MapPoint document is possible, I think we would just need to iterate and properly reference the object and .Activate, but we'll leave this exploration for a future article or as follow-up below.
The purpose of this example application is fairly simple -- show a map in Excel that is updated based on some action in Excel.
First, DOWNLOAD THE SAMPLE APP HERE -- Sample Excel MapPoint Application from MapForums
Unzip the contents - two files Excel MapPoint Automation v1.xlsm and Info.ptm and place them in a folder.
The sample app is not sophisticated by any means, but it serves its purpose to explain the code. The app is driven by the cell E3. This is a drop-down created using Data | Data Validation | and then choosing to Allow: List. This is set to reference the table in =$A$2:$A$53.
When the Excel workbook Excel MapPoint Automation v1.xlsm is opened, it also opens the MapPoint PTM file Info.ptm in the background and leaves it open for use later. This is accomplished with the following code in the ThisWorkbook section of the Excel document.
Note that this is hard-coded for MapPoint 2013 North America (.NA.19). Adjust for your purposes as necessary. Also note that the line 'sAPP.Visible is commented out. By default, MapPoint is opened in "invisibility mode" and is not visible to the user. Uncomment if you wish to see it or allow the user to interact with MapPoint.Code:Dim sAPP As MapPoint.Application Private Sub Workbook_Open() Set sAPP = CreateObject("MapPoint.Application.NA.19") sAPP.OpenMap (Application.ActiveWorkbook.Path & "\Info.PTM") 'sAPP.Visible = True End Sub
Whenever the drop-down value is changed (actually whenever any value in the worksheet is changed) is triggers the routine
This checks to see if it was actually cell E3 that was modified and triggers the heart of the program, module CopyPasteMap.Code:Private Sub Worksheet_Change(ByVal Target As Range) If Target.Column = 5 And Target.Row = 3 Then CopyPasteMap End If End Sub
The code is fairly self-explanatory and I'll mostly let the code above do the talking, but if you have any specific questions about anything, please post below and I'll try to answer as soon as possible.Code:Public Sub CopyPasteMap() Dim APP As MapPoint.Application Dim MAP As MapPoint.MAP Set APP = GetObject(, "MapPoint.Application.NA.19") Set MAP = APP.ActiveMap APP.Height = Cells(7, 6) APP.Width = Cells(8, 6) Dim ofr As MapPoint.FindResults Dim loc As MapPoint.Location Dim ws As Worksheet Set ws = Application.ActiveSheet Set ofr = MAP.FindPlaceResults(Cells(3, 6)) Set loc = ofr(1) loc.GoTo MAP.Altitude = Cells(6, 6) MAP.CopyMap Range("I6").Select ws.Paste End Sub
The macro reads all of it's settings from column F (column 6 in the code) in the Excel worksheet. The dropdown drives a lookup to the zip code, which the macro will zoom to. The code is also using Height/Width and Altitude settings to control the map. Then it copies and pastes the map to a location in Excel. Pretty simple right?
So, for the millions of Excel power-users/macro writers out there, using this example above (and a MapPoint license for each computer), adding an interactive map to Excel is quite do-able, with just a dozen or so lines of code.
As a follow-up to this article, we will
- delete previous map images pasted into the Excel worksheet
- when the drop-down triggers CopyPasteMap, add some error-catching code to detect if the PTM is not open, and re-open it
- in the same vein, when the Excel macro is opened, check to see if the PTM map is already open, and if so, not open a new instance of the PTM map
- investigate automation of a MapPoint Map object embedded into Excel
Are you using this or similar techniques? Please let us know how things go and if you have any suggestions and what you would like to see in Part 2 of this article!
Last edited by Joe4; Nov 8th, 2012 at 04:26 PM.