View Single Post
  #3   Report Post  
Posted to microsoft.public.excel.programming
Steve Hieb Steve Hieb is offline
external usenet poster
 
Posts: 27
Default File size problem: standard remedies not working.

Are there any other things I could try to reduce the filesize? Some
common problems you see?


This is usually simple and has worked for me several times. Select
all your worksheets, right-click on sheet tab, select Copy to a New
workbook. You'll also need to import any modules, forms, etc. You
should notice a modest size reduction. You might need to resolve any
formula links and watch for issues w/ any ActiveX objects used on the
sheets.

A more painful, but usually much more effective approach is similar.
Copy all the cells on each sheet to new/empty sheets and paste only
the formulas. Then carefully reformat everything and recreate any
ActiveX or form buttons, etc.

Here's why. You can test this for yourself. Usually many formats are
tried and later abandoned during a project's development. Each time a
new format is used it usually increases the file size ... over time
this can be excessive. The real mystery why the file remains bloated
even if the format is later not used. Example: if you format a cell
w/ red text then decide to change it back to Automatic you've just
increased the file size - don't ask me. Obviously, format changes
often happen over and over during the creation process causing
bloating. FYI, it seems to happen w/ most formatting, not just
colors. Although sometimes painful, I've seen substantial reductions
using this technique. Also have learned to be very selective w/
formating from the beginning (i.e. do it last).

Other things to consider. I'd make sure you've deleted any unused
named ranges and objects (e.g. charts, text boxes, images, etc.).
Consider writing code to list what's on each sheet ... maybe some
things are there, but not visible to the naked eye. You could use
code to quickly delete them all if you know there shouldn't be any.

HTH,
Steve Hieb