Results 1 to 9 of 9
  1. #1
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Good morning

    I have been asked to maintain an Excel workbook which has 255 worksheets and is a Company world fact book. On each page is a picture of a map and a flag and all are .BMP images and I imagine that this is why the workbook is very bloated.

    Removing them is not an option so I wondered if there was any way in code to go through the workbook and change any .BMP to .GIF?

    TIA
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  2. #2
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Morning all

    I had an idea that if I compressed the pictures it may reduce the bloat in the workbook so I have tried to record a macro as shown below

    Code:
    Sub CompressPictures()
    '
    ' CompressPictures Macro
    ' Macro recorded 05/11/2009 by steve
    '
    ' Keyboard Shortcut: Ctrl+o
    '
        ActiveSheet.Shapes("Picture 166").Select
        Selection.ShapeRange.PictureFormat.Brightness = 0.5
        Selection.ShapeRange.PictureFormat.Contrast = 0.5
        Selection.ShapeRange.PictureFormat.ColorType = msoPictureAutomatic
        Selection.ShapeRange.PictureFormat.CropLeft = 0#
        Selection.ShapeRange.PictureFormat.CropRight = 0#
        Selection.ShapeRange.PictureFormat.CropTop = 0#
        Selection.ShapeRange.PictureFormat.CropBottom = 0#
    End Sub
    to apply it to all of the 255 pages I first selected 'all sheets' but it would not let me select a picture whilst all sheets were selected. I therefore recorded this macro on one sheet but how do I apply it to the whole workbook? I have tried changing Activesheet to ActiveWorkBook but i get a debug error?

    Any ideas please?
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    If you study the VBA code, you will see that the Compress Pictures action has not been recorded, so the macro will not do what you want.
    There is no support for compressing pictures in Excel VBA.


  4. #4
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post
    Quote Originally Posted by stevehocking View Post
    Morning all

    I had an idea that if I compressed the pictures it may reduce the bloat in the workbook so I have tried to record a macro ...
    If you can determine the location of the original bmp files from the worksheet name, and can figure out how to use the code found here, it will possibly be doable.
    Regards
    Don

  5. #5
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts
    Have you tried (because I haven't) using the save options to compress pictures? I've done this in Word/PPT, though not with bmps and it makes a HUGE difference.
    In the Save As dialog find the Tools button, choose the Compress Pictures option (and you'll want to investigate which options work for you).
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  6. #6
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by wdwells View Post
    If you can determine the location of the original bmp files from the worksheet name, and can figure out how to use the code found here, it will possibly be doable.
    Hi Don

    Thanks for the pointer, I have downloaded the file but me thinks that this is way above my head. Having said that I have made a copy of the work book onto a USB and will dabble at the weekend.
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  7. #7
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by WebGenii View Post
    Have you tried (because I haven't) using the save options to compress pictures? I've done this in Word/PPT, though not with bmps and it makes a HUGE difference.
    In the Save As dialog find the Tools button, choose the Compress Pictures option (and you'll want to investigate which options work for you).
    Hi Catherine

    Thanks for that, I did not even realise that the tools option existed in the 'save as' menu, as they say you learn something new everyday.

    I did try it but I think as Hans pointed out above this does not work with Excel files as it made no difference whatsoever whichever options I picked.
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  8. #8
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post
    Quote Originally Posted by stevehocking View Post
    Hi Don

    Thanks for the pointer, I have downloaded the file but me thinks that this is way above my head. ...
    I'm afraid that the VB project has me stumped at the moment as well. Perhaps some of our more astute Loungers can offer some help.
    Regards
    Don

  9. #9
    Plutonium Lounger Leif's Avatar
    Join Date
    Dec 2000
    Location
    U.K.
    Posts
    14,010
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Just for fun, I created a simple worksheet with a 1.2MB .bmp graphic inserted. Saved file was 1,268KB

    Saved it as an .html file, during which process the .bmp was converted to a .png.

    Closed and re-opened the saved .html file up, saved as .xls (with the Tools > Compress option), and the resulting new file was 427KB.

    Whether or not this helps or gives you any ideas is another matter!

    (Using Excel 2002)

Posting Permissions

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