Excel Explosion

[Dcolecpa]

I've got a spreasheet (excell 2k win 2k) with 14 tabs, containing 1 or two
pages schedules in each tab along with one pivot table. It really doesn't
contain a lot of data. I keep version control of the results as
filename_v1.0, filename_v2.0, etc. With no change in the layout (I did add a
short macro) v7.0 is about 1 meg in size and v8.0 is 30 megs. (I did add a
short macro)

Does any one have an idea why it would explode in size like that? and how to
fix it?

[Bill Martin]

Dcolecpa wrote:
> I've got a spreasheet (excell 2k win 2k) with 14 tabs, containing 1 or two
> pages schedules in each tab along with one pivot table. It really doesn't
> contain a lot of data. I keep version control of the results as
> filename_v1.0, filename_v2.0, etc. With no change in the layout (I did add a
> short macro) v7.0 is about 1 meg in size and v8.0 is 30 megs. (I did add a
> short macro)
>
> Does any one have an idea why it would explode in size like that? and how to
> fix it?

--------------------------

One question is whether it's using 30MB to store real stuff, or just empty
cells. Go to each sheet and hit Ctrl-End. That will move the cursor to what
Excel thinks is the last cell used on that page. If that's about where you
think the data ends then all is well. If it goes way down and includes several
thousand empty rows, then that's all junk being stored. To fix it you need then
to delete all the unused rows, store the file and exit Excel, then reopen the
file. At that point you should see Ctrl-End go to the right place.

As I mentioned, you need to do this for every sheet.

Good luck...

Bill

[Jim Rech]

I don't know why the workbook increased by 30 times in size but I do know
that merely opening the VBE can have 'interesting' size effects.

Try this:

1. Change your default Sheets in Workbook to 255 under Tools, Options,
General.
2. With the VBE closed add a new workbook and save it as "NoViewVBE.xls".
3. Open the VBE and close the VBE.
4. Save the same workbook as ViewVBE.xls.
5. Compare the sizes of these two 'empty' files.

In my case ViewVBE is 400k larger. Obviously opening the VBE initializes
the VB module for each sheet and adds a lot of junk to the file. I find
that if I open ViewVBE.xls with the VBE closed and immediately save it, the
size shrinks to almost the size of the first file. I don't know if this has
any bearing on your situation so FYI.

--
Jim
"Dcolecpa" <Dcolecpa@discussions.microsoft.com> wrote in message
news:D0B78D86-3A96-4457-8A05-792A390C5662@microsoft.com...
| I've got a spreasheet (excell 2k win 2k) with 14 tabs, containing 1 or two
| pages schedules in each tab along with one pivot table. It really doesn't
| contain a lot of data. I keep version control of the results as
| filename_v1.0, filename_v2.0, etc. With no change in the layout (I did
add a
| short macro) v7.0 is about 1 meg in size and v8.0 is 30 megs. (I did add
a
| short macro)
|
| Does any one have an idea why it would explode in size like that? and how
to
| fix it?

[Dcolecpa]

Nice try, but no luck

"Bill Martin" wrote:

> Dcolecpa wrote:
> > I've got a spreasheet (excell 2k win 2k) with 14 tabs, containing 1 or two
> > pages schedules in each tab along with one pivot table. It really doesn't
> > contain a lot of data. I keep version control of the results as
> > filename_v1.0, filename_v2.0, etc. With no change in the layout (I did add a
> > short macro) v7.0 is about 1 meg in size and v8.0 is 30 megs. (I did add a
> > short macro)
> >
> > Does any one have an idea why it would explode in size like that? and how to
> > fix it?
>
> --------------------------
>
> One question is whether it's using 30MB to store real stuff, or just empty
> cells. Go to each sheet and hit Ctrl-End. That will move the cursor to what
> Excel thinks is the last cell used on that page. If that's about where you
> think the data ends then all is well. If it goes way down and includes several
> thousand empty rows, then that's all junk being stored. To fix it you need then
> to delete all the unused rows, store the file and exit Excel, then reopen the
> file. At that point you should see Ctrl-End go to the right place.
>
> As I mentioned, you need to do this for every sheet.
>
> Good luck...
>
> Bill
>

[Dcolecpa]

Thanks Jim. Good approach, we did find out what blew up the spreadsheets. I
had put company logos ( jpeg's) in a few pages somehow they got corrupted,
delete them and the workbook is back to normal.
Thanks again

"Jim Rech" wrote:

> I don't know why the workbook increased by 30 times in size but I do know
> that merely opening the VBE can have 'interesting' size effects.
>
> Try this:
>
> 1. Change your default Sheets in Workbook to 255 under Tools, Options,
> General.
> 2. With the VBE closed add a new workbook and save it as "NoViewVBE.xls".
> 3. Open the VBE and close the VBE.
> 4. Save the same workbook as ViewVBE.xls.
> 5. Compare the sizes of these two 'empty' files.
>
> In my case ViewVBE is 400k larger. Obviously opening the VBE initializes
> the VB module for each sheet and adds a lot of junk to the file. I find
> that if I open ViewVBE.xls with the VBE closed and immediately save it, the
> size shrinks to almost the size of the first file. I don't know if this has
> any bearing on your situation so FYI.
>
> --
> Jim
> "Dcolecpa" <Dcolecpa@discussions.microsoft.com> wrote in message
> news:D0B78D86-3A96-4457-8A05-792A390C5662@microsoft.com...
> | I've got a spreasheet (excell 2k win 2k) with 14 tabs, containing 1 or two
> | pages schedules in each tab along with one pivot table. It really doesn't
> | contain a lot of data. I keep version control of the results as
> | filename_v1.0, filename_v2.0, etc. With no change in the layout (I did
> add a
> | short macro) v7.0 is about 1 meg in size and v8.0 is 30 megs. (I did add
> a
> | short macro)
> |
> | Does any one have an idea why it would explode in size like that? and how
> to
> | fix it?
>
>
>