PDA

View Full Version : Using Excel 2007 Macro to Update Maps integrated with MP 2010



crespo0542
07-26-2011, 01:35 PM
Hi all,

I am a new user to MapPoint 2010. I am so glad that I can find such a useful forum. I searched in this forum for a couple of hours and found some clues to my problem, but still could not get exactly I want.

My problem here is I have data in Excel 2007, which will be updated daily, and the data is to be used to create a map from MapPoint2010. Currently I am creating new maps manually. What I would like to have is to somehow to update the map in Excel file by click a button with a macro embeded.

Here is my sample data in sheet1. D is simply B-C
A B C D
Midwest 1000 500 500
and I have the sheet2 with data:
A B C D C E
CHI CHicago IL 500 250 250
E is D-C.
So my map would be like a colored shadown showing the volume D in sheet1, and a pie chart showing volume E in sheet2.

I found people saying Excel 2007 macro cannot extract a map from MapPoint 2010. I do not know whether it is true. I tried some macros, and they did not work.

Any help would be greatly appreciated.

Thanks.

RepublicDirect
07-26-2011, 02:25 PM
It's possible to extract a map from MapPoint 2010 (no matter which version of Excel). It's a bit crude, because you have to use the web page creation feature which then generates an html page and then a gif file of the map you needed.

I am doing this now, but I am only making a simple map with two waypoints and a route with my macro. I'm not sure what you mean with the sample data you provided. It sounds more like you're talking about creating graphs?

crespo0542
07-26-2011, 02:32 PM
It's possible to extract a map from MapPoint 2010 (no matter which version of Excel). It's a bit crude, because you have to use the web page creation feature which then generates an html page and then a gif file of the map you needed.

I am doing this now, but I am only making a simple map with two waypoints and a route with my macro. I'm not sure what you mean with the sample data you provided. It sounds more like you're talking about creating graphs?

Thanks for the reply.

The map I am creating in MapPoint is like import the sheet1 and create shadows with Column D, and then import sheet 2 Column F as pie charts on top of the shadows. And now I would like to have the map in Excel instead of MP, so that I can look at the map combined with data. I think this is a later step to do.

Can you please post your macro to help me first create a map from MP to the Excel file?

Thanks a lot.

RepublicDirect
07-26-2011, 04:11 PM
Well if you still want to extract a map from MapPoint, I think what you're looking for is the SavedWebPages function.

So record / code the macro you need to make your map, and then add this code at the end:


Set objSW = objMap.SavedWebPages.Add("C:\MAPSOUT\HTMLOUTPUT", _
Nothing, "webpagenamehere", _
True, False, False, 500, 450, False, False, False, False)In my code I'm ignoring all the webpage info, which I filled with placeholders. The only thing I ever change here is the 500 & 450 which define the map size in pixels.

So when the macro is ran, the code above will make the following file: C:\MAPSOUT\HTMLOUTPUT_files\image_map.gif, which will be your map. From there it should be easy to add it to a spreadsheet as an image.

*Note: there may be an easier route to get what your looking for via the COM Add-In for Excel, but I don't know much about using it that way.

crespo0542
07-27-2011, 07:54 AM
Thank you. I will try it later.

Winwaed
07-27-2011, 07:57 AM
Depending on what you mean by "extract a map", but embedding a MapPoint map control in your spreadsheet might be one option. You could then get the macro to tell the Link to update as/when required.

Depends very much on your application whether this is suitable: It will give you an embedded interactive map; but everyone who reads the sheet will need MapPoint installed.

Eric Frost
07-27-2011, 10:28 AM
If you just want to paste into an Excel worksheet or report CopyMap will work.



oMap.CopyMap

MySheet.Paste _
Destination:=NewSheet.Cells(4, 4)


You should be able to do the same sort of thing to copy into other apps like Word or Powerpoint.

Eric

Eric Frost
07-27-2011, 10:30 AM
I wrote an article recently which has a macro for automatically updating and creating maps, it uses the Save As Web Page method and then actually FTP's the file to a web server for sharing :-)

http://www.mapforums.com/automatically-publishing-mappoint-maps-mobile-devices-14983.html

Eric

crespo0542
07-28-2011, 01:26 PM
If you just want to paste into an Excel worksheet or report CopyMap will work.



oMap.CopyMap

MySheet.Paste _
Destination:=NewSheet.Cells(4, 4)


You should be able to do the same sort of thing to copy into other apps like Word or Powerpoint.

Eric

Thanks, Eric.
Finally I used the CopyMap, and have figured it out, a littble bit defference in my code though.

Thanks a lot for those who replied.