VBA Code to reduce file size of Workbook

[malik641]

Does anybody know where I can find some code to reduce the file size of a workbook? I remember seeing it on the web somewhere, but I can't find it. I want to say it was John Walkenbach's code, but I can't say for sure.

The was useful for workbooks that maybe had shapes / images / etc that takes up a lot of memory in workbooks, but after you delete all of them the file size doesn't get any smaller. That's where this code came in.

Has anybody else ever heard of this?

Thanks in advance
Joseph

[lucas]

Hi Joseph, have you tried Jakes code:

http://vbaexpress.com/kb/getarticle....0d478c4f40c6da

This will try to reduce the size of the Excel file by forcing Excel to recalculate the used range for each sheet.

[malik641]

Hey Steve,

Just tried it and it's not exactly what I'm looking for. I have a worksheet that has a bunch of objects (346) that were added from the control toolbox. I can delete the shapes easily, but it doesn't help so much with the size. I guess now I'm just looking for the reason this file is 10MB. It only has 6000 rows of data and all the other worksheets are simply pivot tables of the main sheet.

I copied the main worksheet into a new book and the book is 3MB as a result...so where does the 7MB come from?

Thanks for the code. I'll remember it for future reference.

[lucas]

sometimes the vba will bloat the file too. Try exporting all of your modules and userforms, etc. and delete them from the book and then import them back into the workbook.....see if that helps...

guess I don't have to tell you to use a copy for such experiments......

[malik641]

There is no code in this workbook. Never was, I don't think. I'm the only one at my job who knows how to do that and I never touched this workbook.

And no, you don't have to tell me to use a copy I've learned my lesson long ago.