PDA

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



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

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)

Griffin54
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")
objMap.DataSets(2).UpdateLink
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.Save
objSW.AutoResave = True
objMap.Save
objMap.SaveAs "MaptoDisplayShare", geoFormatHTMLMap, True

objMap.Saved = True


MPApp.Quit
Set MPApp = Nothing

Range("F10").Select
ActiveSheet.Pictures.Insert( _
"C:\Temp\MaptoDisplayShare_files\image_map.gif").Select


End Sub

Thanks!