AddPicture w/ Excel 2007 (2007 vs 2003)

Topic

New Reply

I am (slowly) in the process of converting my Excel 2003 spreadsheets to Excel 2007. For the most part this has been fairly painless. I have run across one particular annoyance so far, though.

I use VBA from an Access database to generate a “status report” spreadsheet where in each row I insert a little graphic “status bar” (as a picture) that shows the progress of that row’s item. In Excel 2003 this worked reasonable well — even though I was adding as many as several hundred little pictures to the spreadsheet.

In converting this to Excel 2007 (even using the 2003 file format), I’ve found that the Excel Object Model VBA syntax for adding a picture and placing it is somewhat different. I’ve figured out the difference and recoded accordingly (using ActiveSheet.Shapes.AddPicture rather than ActiveSheet.Pictures.Insert — the latter works in Excel 2007, but it appears you have to position it with separate commands; in Excel 2003 it was placed over the selected cell; with AddPicture, the position is specified as as arguments of the method). Anyway, it works, but the annoyance is that it is EXCRUCIATINGLY slow in Excel 2007 (compared to 2003). Any suggestions for how this can be sped up???

I’m considering scrapping the graphics completely and just background filling one or progressively more of the cells that are currently under the graphic to show the progress. This will result in an uglier spreadsheet (the graphics had nice shaded arrows showing progress), but seems like it might be the only option for speeding things up…

Thanks,

Reply | Quote

Replies

If you put the progress in a cell as a number, you can use the new databars feature of the conditional formatting. Should be quite pretty

Reply | Quote

Thanks for the tip. I’ll take a look at that alternative. Sounds like it might be a more elegant solution than the graphics option (even if placing the pictures were faster!). We do still have some Excel 2003 users (viewers) of this spreadsheet, so if it’s unique to the Excel 2007 file format, I’ll have to wait until everyone’s off Excel 2003.

Reply | Quote