PDA

View Full Version : VBA Code to reduce file size of Workbook



malik641
12-05-2008, 09:21 AM
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
12-05-2008, 10:02 AM
Hi Joseph, have you tried Jakes code:

http://vbaexpress.com/kb/getarticle.php?kb_id=83&PHPSESSID=c85625d0c03285e03b0d478c4f40c6da



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

malik641
12-05-2008, 11:45 AM
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
12-05-2008, 11:50 AM
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
12-05-2008, 01:08 PM
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.