View Full Version : Need Excel VBA code to update linked data

11-06-2011, 12:00 PM
I have an Excel worksheet with an embedded map and a named range called DatatoDisplay. The map was created to show the data for Sales Territories and the data to be displayed shows a metric for some of the territories. Each time data in the named range is updated and the Update Map button is pressed I would like the map to update to reflect the new data. The data for the map is linked to the named range.

I think this is pretty simple just new to MapPoint and not sure what VBA is required. Any help would be great.

Mattys Consulting
11-11-2011, 10:58 AM
Actually, there isn't any command that I've come across that makes that work from code - not even Windows API (SendMessage)

11-11-2011, 11:37 AM
Thanks for your reply. I have been working on this and using some code I found on the forum - came up with an alternate solution using an external map, updating it and then saving as a .gif and inserting the .gif file into the Excel tab.

It works but is slow - any thoughts on how to speed this up.

Private Sub CommandButton5_Click()
'open ptm files, update linked data, and save
Dim MPApp As MapPoint.Application
Dim objMap As MapPoint.Map
Set MPApp = CreateObject("MapPoint.Application")
MPApp.Visible = True

Set objMap = MPApp.ActiveMap
MPApp.OpenMap (ThisWorkbook.Path & "\TestMapShareData.ptm")
Dim objSW As MapPoint.SavedWebPage
Set objSW = objMap.SavedWebPages.Add("C:\Temp\MaptoDisplayShare", _
objMap.Location, "MaptoDisplayShare", _
True, False, True, 800, 480, False, True, False, True)

objSW.AutoResave = True
objMap.SaveAs "MaptoDisplayShare", geoFormatHTMLMap, True

objMap.Saved = True

Set MPApp = Nothing

ActiveSheet.Pictures.Insert( _

End Sub