Code to change .BMP's

Topic

New Reply

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

Reply | Quote

Replies

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?

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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).

Reply | Quote

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.

Reply | Quote

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)

Reply | Quote