Solved: bloating of file

[philfer]

I have written some code to automate some calculations at work.

It all works fine but at the end of the calculation the file size is 7.5megs. I use ActiveWorkbook.Save at the end (of 31 pages of code) to save the file.

However, when I re-open the file and press save again ( I do nothing else just open it and press save) the file size shrinks to 6.6 megs.

Has anyone come across this before or do you know why it is happening.

Like I said I dont do anything just open it and press save and it shrinks.

I use Activeworkbook.SaveAs and ActiveWorkbook.Save but it still wont save as the smaller size.

Help anyone?!?!?!?

[Paul_Hossler]

Try Tools, Options, Save tab, and disable "Auto Recover" and see if that helps

Paul

[philfer]

that didnt work

its really boggling my mind

[rory]

Which version of Excel?

[philfer]

I believe it is 2000.

I am not at work today but I think this is correct

[Simon Lloyd]

Take a look at this link for de-bloating a file http://www.vbaexpress.com/forum/archive/index.php/t-496.html

[philfer]

Thanks for the suggestion

However I feel its not really practical to recreate the workbook regularly.

I cant help but feel there is a more practical and sensible answer out there. There must be a reason Excel is doing this and thus there must be a way to capture and avoid or solve it.

Someone out there must have the answer

Help me Obi Wan Kanobi youre my only hope

[tstav]

Hi Philfer,
if recreating the file is a lot of work (and I agree that it sometimes is and gets to be unpractical, as you say) maybe you'd like to check out DRJ's KB entry http://www.vbaexpress.com/kb/getarticle.php?kb_id=83#instr .
All you'll have to do is run a macro.
Could make an unbelievable difference to your file's size.
If you are using Excel 2007, change the IV65536 address that Jake is using
.Range(Cells(1, LastCol + 1).Address & ":IV65536").Delete
to

.Range(.Cells(1, LastCol + 1), .Cells(.Rows.Count, .Columns.Count)).Delete


Edit: It goes without saying that you always keep a backup file.

[Krishna Kumar]

Check the autofilter mode is on. If it's on, make it off before closing the file.

[rory]

You might try adding a loop at the end of your code before the save to explcitly reset the usedrange of each sheet - something like:
Dim rng as range, wks as worksheet
For each wks in activeworkbook.worksheets
set rng = wks.usedrange
next wks
set rng = nothing
activeworkbook.save
just to see if that helps.