Welcome to MapForums!

Register, sign in, or use Facebook Connect above to join in and participate in the forum.

When you are signed in, this message, the ads in this row, and the red-underlined link ads all go away.

Subscribe to receive our newsletter.
Subscribe Unsubscribe
Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Sample Excel Application - Automating MapPoint and Pasting Map Images into Excel - Part 1 of 2

This is a discussion on Sample Excel Application - Automating MapPoint and Pasting Map Images into Excel - Part 1 of 2 within the MP2K Magazine Articles forums, part of the Map Forums category; This articles shows a relatively simply way to control MapPoint and paste map images into Excel based on some event ...

  1. #1
    Eric Frost's Avatar
    Eric Frost is offline Administrator Black Belt
    Join Date
    Jul 1992
    Posts
    5,094
    Blog Entries
    4

    Sample Excel Application - Automating MapPoint and Pasting Map Images into Excel - Part 1 of 2

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

    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 Eric Frost; 11-03-2012 at 09:58 AM.
    ~ Order MapPoint MapPoint 2013 Here and Get Free Access to the MapForums Downloads ~
    ~~
    ~ Upgrade to Get Access to the MapForums Downloads ~

  2. #2
    jake.masters is offline Junior Member White Belt
    Join Date
    Oct 2012
    Posts
    3

    Re: Sample Excel Application - Automating MapPoint and Pasting Map Images into Excel - Part 1 of 2

    This is fantastic and exactly what I was looking for, and is very easy to implement. MapPoint and Excel work seamlessly together, much more than I had anticipated. This is a great article, and I look forward to seeing updates in the future. Thanks for all of your help Eric!

  3. #3
    jake.masters is offline Junior Member White Belt
    Join Date
    Oct 2012
    Posts
    3

    Re: Sample Excel Application - Automating MapPoint and Pasting Map Images into Excel - Part 1 of 2

    Hi everyone, here is an update to the story. The Excel dashboard is complete, and here is a screenshot of the map function working inside of Excel.



    Thanks again Eric!
    Attached Images Attached Images
    Last edited by Eric Frost; 11-05-2012 at 01:53 PM.

  4. #4
    Eric Frost's Avatar
    Eric Frost is offline Administrator Black Belt
    Join Date
    Jul 1992
    Posts
    5,094
    Blog Entries
    4

    Re: Sample Excel Application - Automating MapPoint and Pasting Map Images into Excel - Part 1 of 2

    Wow this looks really awesome. I'll hire you to do my next Excel application!
    ~ Order MapPoint MapPoint 2013 Here and Get Free Access to the MapForums Downloads ~
    ~~
    ~ Upgrade to Get Access to the MapForums Downloads ~

  5. #5
    jgold4 is offline Junior Member White Belt
    Join Date
    Jan 2013
    Posts
    2

    Re: Sample Excel Application - Automating MapPoint and Pasting Map Images into Excel - Part 1 of 2

    I have to be honest with you, that looks awesome. Totally going to change the way I dashboard data.

    That said, I have a couple of questions.

    I have a dashboard that shows store information, driven by a store number. I have a separate mappoint file I use with those same stores on it. What I would like to do is embed a mappoint map on the dashboard like above, but when I select a different store, have the map change to show that store in the map. In your code, I see you are driving it by zip code, however would it be possible to drive it by lat-long if i looked those up into the dashboard somewhere?

    Also I assume I can modify the path of the ptm to point to a common location so that anyone can have that functionality, however, offline, will the map embed or will I always have to have a live connection? Does that bloat the file at all (my dashboard is 2mb, and the ptm is 5mb?).

    Last question - I am using office 2007 and MP2009. I assume I change the .19 to .16 for my version. Any issues with office 2007 and your code?

    thanks, I am a newb at all this VB stuff.

  6. #6
    Eric Frost's Avatar
    Eric Frost is offline Administrator Black Belt
    Join Date
    Jul 1992
    Posts
    5,094
    Blog Entries
    4

    Re: Sample Excel Application - Automating MapPoint and Pasting Map Images into Excel - Part 1 of 2

    >> What I would like to do is embed a mappoint map <

    I don't think you will have great success when you "embed" the map, though it might be useful for certain situations sharing within a workgroup or limited number of colleagues.

    You could definitely modify the code to drive it with lat/lon.

    Regarding the common location and blowing up your file size, it shouldn't be an issue if you are using MapPoint to generate images to paste into the map as demonstrated in the article.

    Finally, there should be no issues with anything we've discussed so far with versions (MapPoint or Excel).

    Happy Mapping!

    Eric
    ~ Order MapPoint MapPoint 2013 Here and Get Free Access to the MapForums Downloads ~
    ~~
    ~ Upgrade to Get Access to the MapForums Downloads ~

  7. #7
    jgold4 is offline Junior Member White Belt
    Join Date
    Jan 2013
    Posts
    2

    Re: Sample Excel Application - Automating MapPoint and Pasting Map Images into Excel - Part 1 of 2

    Quote Originally Posted by Eric Frost View Post
    >> What I would like to do is embed a mappoint map <

    I don't think you will have great success when you "embed" the map, though it might be useful for certain situations sharing within a workgroup or limited number of colleagues.

    You could definitely modify the code to drive it with lat/lon.

    Regarding the common location and blowing up your file size, it shouldn't be an issue if you are using MapPoint to generate images to paste into the map as demonstrated in the article.

    Finally, there should be no issues with anything we've discussed so far with versions (MapPoint or Excel).

    Happy Mapping!

    Eric

    Thanks Eric - I dont think I realized that the code creates a static picture. I thought it was embedding the map with the prescribed attributes each time it runs. I really want to have it where I put in the store number, it pulls the lat long off my table and shows me zoomed to a preset level that store on a map (that has all my other stores). I know there are some tool sets you can purchase that do something like this, I suspect this would be useful for matching customers to closest locations, but this is an enhancement, not something necessary and I will never get permission to pay for it.

  8. #8
    Eric Frost's Avatar
    Eric Frost is offline Administrator Black Belt
    Join Date
    Jul 1992
    Posts
    5,094
    Blog Entries
    4

    Re: Sample Excel Application - Automating MapPoint and Pasting Map Images into Excel - Part 1 of 2

    The way it's written above, each computer needs to have MapPoint installed ($249), as it is using MapPoint to generate the image each time. How many potential users are there?

    It's possible to pre-generate all the maps and store them in Excel. I helped a client set something up like this a while back, I think they had 150 locations/maps. How many stores are there? Then when you select a store, it copies the map image for that store into the dashboard. If there are a HUGE number of stores, this could bloat the Excel file, but users would not have to install MapPoint.

    So, there are trade-offs, and the # of users and the # of stores are definitely factors to consider. Also, how often the data changes is also a factor.

    Let us know how it goes and if we can be of any assistance! Do you have MapPoint now or planning to download the trial and install it?

    Eric
    ~ Order MapPoint MapPoint 2013 Here and Get Free Access to the MapForums Downloads ~
    ~~
    ~ Upgrade to Get Access to the MapForums Downloads ~

  9. #9
    Donna111 is offline Junior Member White Belt
    Join Date
    Jan 2014
    Posts
    2

    Re: Sample Excel Application - Automating MapPoint and Pasting Map Images into Excel - Part 1 of 2

    HI there
    There are so many useful information in this topic.I am new in Map.And i am testing some related program which supports to
    process excel files directly.Some of my client are the users of Map.
    They told me that they want to get a tool which supports to save or create excel files in Map.So is there any recommendations?
    Thanks for any suggestions.

  10. #10
    Eric Frost's Avatar
    Eric Frost is offline Administrator Black Belt
    Join Date
    Jul 1992
    Posts
    5,094
    Blog Entries
    4

    Re: Sample Excel Application - Automating MapPoint and Pasting Map Images into Excel - Part 1 of 2

    Donna111,

    Welcome to Map Forums!!

    MapPoint has a function to "Export to Excel". Have you explored this and know what it is capable of creating?

    If you have some specific needs, we'd be happy to help you, please post more information in a new thread.

    Thanks!
    Eric
    ~ Order MapPoint MapPoint 2013 Here and Get Free Access to the MapForums Downloads ~
    ~~
    ~ Upgrade to Get Access to the MapForums Downloads ~

Page 1 of 2 12 LastLast

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Grabbing a map image with a route and pasting into Excel
    By Eric Frost in forum Bing Maps and MapPoint Web Service
    Replies: 2
    Last Post: 04-19-2015, 12:02 AM
  2. Copying and pasting part of a map - is it possible?
    By superbeds in forum MapPoint Desktop Discussion
    Replies: 2
    Last Post: 07-24-2009, 09:11 AM
  3. Sample Excel Calling MapPoint's ActiveX Controls (VBA+VSTO)
    By anassar in forum MapPoint Desktop Discussion
    Replies: 0
    Last Post: 03-14-2005, 03:13 PM
  4. Excel/MapPoint Application Development & Deployment
    By Anonymous in forum MapPoint Desktop Discussion
    Replies: 0
    Last Post: 03-06-2005, 03:16 PM
  5. MPControl embedded in excel - excel loses functionality
    By ruyasan in forum MapPoint Desktop Discussion
    Replies: 2
    Last Post: 05-20-2004, 03:41 AM

Posting Permissions

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


1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27