Sample Excel Application - Automating MapPoint and Pasting Map Images into Excel

[ericwfrost]

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.

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

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.

Whenever the drop-down value is changed (actually whenever any value in the worksheet is changed) is triggers the routine

Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.Column = 5 And Target.Row = 3 Then
    CopyPasteMap
  End If
End Sub

This checks to see if it was actually cell E3 that was modified and triggers the heart of the program, module CopyPasteMap.

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 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.

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!

Eric

 

[gillyr7]

I create maps out of mappoint and paste them into excel as static pictures. I then send these out to other users to view. Using this new method, will the final users viewing the file be able to view the maps interactively with zooming features?

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.

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

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.

Whenever the drop-down value is changed (actually whenever any value in the worksheet is changed) is triggers the routine

Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.Column = 5 And Target.Row = 3 Then
    CopyPasteMap
  End If
End Sub

This checks to see if it was actually cell E3 that was modified and triggers the heart of the program, module CopyPasteMap.

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 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.

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!

Eric

 

[Eric Frost]

I create maps out of mappoint and paste them into excel as static pictures. I then send these out to other users to view. Using this new method, will the final users viewing the file be able to view the maps interactively with zooming features?

Thanks for reading and I hope it will help you!

You could... you could have a drop down or buttons to set the zoom level. Alternatively you could automate MapPoint as an external application rather than pasting the image in MapPoint. In either case however you do need MapPoint installed on the computer or it's not going to work.

Alternatively you could have a hidden page where LOTS of map images are pre-loaded into Excel and the dropdown just swaps images.

hope this helps gives you some ideas!
Eric
MapForums.com for MapPoint

 

[Smitty]

You can now map natively in Excel 2013 with the Power BI add-ins: Power BI Download Add-in - Office.com