compress pictures (Excel 2003 SP3)

[steveh]

Good evening

I have recorded a macro to compress pictures but how would I apply it so that it would compress pictures on all of the WB pages (there are about 270 pages)

Code: Select all

Sub CompressPictures()
'
' CompressPictures Macro
' Macro recorded 05/08/2010 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

TIA

[HansV]

I'm afraid that your code doesn't compress a picture - it just sets some default properties of the picture.

Microsoft has forgotten to add compressing pictures to Visual Basic for Excel, so there is no "official" way to do so through code. You have to resort to a rather dirty trick: displaying the Compress Pictures dialog and clicking OK in it.

Code: Select all

Sub CompressPictures()
  Dim cbr As CommandBarControl
  Set cbr = Application.CommandBars.FindControl(ID:=6382)
  SendKeys "%e~"
  cbr.Execute
End Sub

In Excel 2007, this displays a warning that quality may be reduced, I don't know whether this is the case in Excel 2003 too. To get rid of this, change

Code: Select all

  SendKeys "%e~"

to

Code: Select all

  SendKeys "%e~~"

[steveh]

Thanks Hans

I was having a vain attempt at reducing the size of 1 13 meg WB having tried all other methods that are normally advised, compressing the pictures increased the book by 336kb :-)

Ta

[HansV]

That's a useful result - not!

[ABabeNChrist]

Hi steveh
I’m not sure if this will be of any help but I was having sort of the same problem; my workbooks, they were getting to large. So now I have gotten into the practice of using “FastStone Image Viewer” (Freeware) to reduce the size of my pictures from 2,749 KB to 364 KB, at almost 1/10 the size the picture were still very clear. Pictures have a habit of taken up way a whole lot of memory. I am now able to do a 100+ photos at one time in about 3 minutes. It’s very easy and quick to use. I saw when Hans posted comments about this software (Post=13087), I then gave it a try and I really like it. Of course you will have to remove picture, reduce size then re-insert to your workbook you are refering to. again just a thought.

FastStone Image Viewer - image browser, converter and editor with nice interface; includes lots of tools.
Download from FastStone Image Viewer (portable version available).

[steveh]

Thanks for the advice

I never thought of that (which I should have done before putting them in the first place, each page has 2 pictures so removing 500+ will be tedious, but this is how you learn your lessons :-) ) and I already have Fast Stone on my PC