Overbloated EXCEL workbook?

[Kimed]

Hi,

I have an application in development that currently has about 50 heavily interconnected sheets (including graphs), several dozens of VB functions and is more than 4 MB size, from which the actual data takes only the small part. And apparently it's not the end. While I've been considering the possibility of splitting the file to several smaller sub-tasks, I wanted to know whether I gain much from it. So I removed about the half of sheets and graphs that the remaining part could work without (the vice versa would be harder, but that's a problem I'll not bore you with) and saved the resulting workbook in a separate file. It was somewhat bigger than I expected. Intrigued, I removed *all* existing sheets (and inserted a new empty one so that Excel would have something to work with), all procedures from VB project, and all references to named ranges, styles and custom menus that the workbook ever had (I think that's all that can be manually removed). The resulting file was still more than 640 KB - while a new empty workbook is only 15. I opened the file in the Notebook... not that I could understand a lot, of course, except that about 2/3 of the file consists of similarly looking 128-byte info blocks. What's more interesting was that in the remaining part of "junk" I discovered names of various controls, VB procedures and variables, including those that were removed from the project long ago during development. I suppose that if I looked inside contents of separate worksheets (and had means to separate the sheep from the goats) I'd see some obsolete junk there as well.

So the question is: is it possible to "compact" an Excel workbook and is it worth the effort? The size per se doesn't bother me *too* much, but the performance of the app sometimes seems to suffer.

Thanks.

 

[SkipVought]

Hi,

Add a new workbook.

Insert new sheets, one for each sheet in your original.

Do not copy sheets to the new workbook. Rather, copy the ranges on the sheet to new sheets.

Export all modules.

Import these modules to the new workbook.

Skip,
[sub]
To be safe on the [red]FOURTH[/red],
Don't take a [red]FIFTH[/red] on the [red]THIRD[/red]
Or you might not come [red]FORTH[/red] on the [red]FIFTH[/red]
[red][highlight blue]FORTH[/highlight][/red][white][highlight red]WITH[/highlight][/white] [/sub]

 

[Kimed]

Tried that. With the general amount of controls (and there's plenty in each sheet) that will need renaming, controls that moved out of place or simply refused to copy (what the hell is the difference with those that copied alright, I have no idea), controls that somehow changed the way their captions look (even if font settings and other properties stayed the same), formulas referring to other sheets that started to refer to sheets of the original workbook and other small differences in resulting product's looks and behavior that needs finding and ironing out before the copy can be called similar to the original, I'll certainly keep that method in mind... in case things become *really* desperate. Thanks anyway.

 

[razman10]

What I do is select all of the blank columns to the right of the data and delete the entire columns. Then I select all of the blank rows below the data, and delete the entire rows. Then save the workbook (it'll be much smaller in file size).

This can be done rather quickly once you get used to it!

Razman10