Results 1 to 4 of 4

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

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

  1. #1
    New Member
    Join Date
    Mar 2009
    Posts
    2

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

    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.

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

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

    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 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
    Last edited by Joe4; Nov 8th, 2012 at 05:26 PM.

  2. #2
    New Member
    Join Date
    Sep 2013
    Posts
    1

    Default Re: Sample Excel Application - Automating MapPoint and Pasting Map Images into Excel

    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?






    Quote Originally Posted by ericwfrost View Post
    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.

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

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

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

  3. #3
    New Member
    Join Date
    Feb 2012
    Location
    Chicago
    Posts
    13

    Default Re: Sample Excel Application - Automating MapPoint and Pasting Map Images into Excel

    Quote Originally Posted by gillyr7 View Post
    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

  4. #4
    MrExcel MVP
    Moderator
    Smitty's Avatar
    Join Date
    May 2003
    Location
    Crested Butte, CO
    Posts
    27,398

    Default Re: Sample Excel Application - Automating MapPoint and Pasting Map Images into Excel

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

    Every once in a while, there's a sudden gust of gravity...

    Check out my new book at the Mr. Excel Bookstore!

    Mr. Excel HTML Maker - Post a shot of your sheet

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com