Results 1 to 3 of 3
  1. #1
    4 Star Lounger
    Join Date
    Feb 2001
    Location
    Richland, Washington, USA
    Posts
    407
    Thanks
    0
    Thanked 0 Times in 0 Posts

    AddPicture w/ Excel 2007 (2007 vs 2003)

    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,

  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: AddPicture w/ Excel 2007 (2007 vs 2003)

    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 <img src=/S/smile.gif border=0 alt=smile width=15 height=15>
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  3. #3
    4 Star Lounger
    Join Date
    Feb 2001
    Location
    Richland, Washington, USA
    Posts
    407
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: AddPicture w/ Excel 2007 (2007 vs 2003)

    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.

Posting Permissions

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