Solved: Reduce Template File Size

[fionabolt]

Before deploying a new template I always reduce the file size by exporting all the modules and forms, saving a copy of the template without the modules or forms, and then re-importing them again. This can significantly reduce the size of the template.

However, on a large project this can be quite time consuming.

I'm sure I read somewhere about a piece of freeware that could do this for you.

Anybody know what it's called, or how I can get hold of it?

Thanks

[fumei]

I don't know the answer to the specific question. However, I have a question of my own.

Really? I have never done this - exporting modules and forms, and then importing them.

Can you give an example of file reduction size, from what to what?

I am trying to understand why this would be.

OK. Yes, it does, although I am not exactly sure why.

A template with 9 userforms, 11 modules.

Original size = 478 Kb
All userforms and modules removed = 135 Kb
Imported back in = 391 Kb.

So, you are right. It does make a difference. And yes, on a very large project I can see this would be quite time consuming.

Again, I am not sure WHY there is a size difference....Tony? I examined the old versus "new" modules, and I can see no difference at all. I thought that maybe exporting does some sort of compression/removal of extra spacing, or whatever. Nope. They are identical. So where/what "goes away" in terms of byte size? Beats me.

I have never looked into any automation of doing this, so again, I can't answer your question. If you find something please let us know.

[TonyJollans]

I heard my name :)

The reason for the size difference, as I understand it, is that when you compile your project, the old compiled code is not deleted so, over time, it grows and grows.

Removing all the modules forces the old compiled code to be deleted and re-importing the source in the modules and then compiling, just the once, makes it as small as it can be.

It may still be a chore but I find it quicker to create a new document and drag and drop all the modules into it, then delete them all from the original document/template, then drag and drop them back.

There probably is some software out there - and it wouldn't be hugely difficult to write some - but I don't know what or where it might be.

[perhol]

Found something here:
http://www.vbaexpress.com/kb/getarticle.php?kb_id=83

And this one i tried. Be carefull when trying Total Cleaning. This will remove all formatting on empty cells:
http://www.ozgrid.com/Excel/ExcelProblems.htm

You could also look here:
http://www.mvps.org/dmcritchie/excel/lastcell.htm#resetall

[Paul_Hossler]

In addition to always using Rob's Excel cleaner, I've also used Bob & Bill's Word code cleaner


http://word.mvps.org/faqs/macrosvba/TemplateBloat.htm

In part:
Obtaining the Word Code Cleaner

The Word Code Cleaner was originally written by Rob Bovey for Excel, then modified to run in Word by Robert Affleck, and has since been updatedby Bill Coan (http://word.mvps.org/AboutMVPs/bill_coan.htm). It will work with Word 97, Word 2000 and Word 2002. The Word Code Cleaner does not work with any version of Word for Macintosh.

There's a shortcut link to take you to the file

http://word.mvps.org/downloads/wordcodecleaner.zip

Paul

Also -- if you're looking for the Excel code cleaner it's at http://appspro.com/Utilities/CodeCleaner.htm

[fionabolt]

The Affleck/Coen Word Code Cleaner works like a dream (PS I'm using 2003).

This will save me so much time. Thank you to everyone who replied. :beerchug:

Fiona