File Size with VBA/XLS

[JCH]

I have created a workbook that had a series of options
that have VBA coding in the background to automate
sections and to do error checking. I noticed after that
the file size for the spreadhseet is nearly 1MB.

I need to decrease the file size, while maintaining all
the functionality that is currently possesses. I know in
Access, you can do a Compact/Repair which dramatically
decreases the file size and rids the "hidden" files from
the database. Is there anything available in Excel 2002
that will generate the same results?

Desparetly Seeking Solutions,
JCH

 

[Tom Ogilvy]

Nothing built in. Rob Bovey has a free addin which attempts to do something
similar.

http://www.appspro.com
go to free downloads.

 

[JCH ...]

I tried running the CodeCleaner.exe and I continuously get the error
indicating that it can't find any projects to clean. I did notice
however that it does not clean code behind sheets, books, which is where
everything resides in this particular case. The error checking is very
limited and the code being run simply is a copy and paste (or a mass
clearing of 15-20 cells).

Everything works fine in the file, I just want to try and trim it down
since it'll be available for download from a website. A 1MB file isn't
too bad with highspeed, but for anyone on dialup, its a nightmare.

Any suggestions? My deadline is tomorrow...so if I can't shrink it down,
then it'll stay at 1MB.

 

[Ulrik Gustafsson]

Hi,

One workaround you can try is to zip the file with
7-zip.( www.7-zip.org).

With the zip manager that comes with 7-zip you can make it a self extracting
exe. The 7-zip format has a high compression ratio. Usually 7-zip compresses
30-70% better than zip format.

I think your file will shrink to somehing like 200-300 KB.

Otherwise if you want to try an excel solution to shrink your file - you can
try the utlity Excel Rebuilder at
http://www.vbusers.com/commercial/Rebuild.asp

I haven't tried it yet - but you can give it a try...
Maybe this utility can help.

Rgds Ulrik