What is happening with my Excel 2003?

[Paulo Martins]

Hello,

I have a Excel file that has 160 sheet’s. Each one contains information
concerning a cost center that i monthly distribute for each director of cost
center and, him returns it again. I work with this file about 2 years without
problems.

Now, when i make copy/paste of the information that comes of the director of
each cost center for my file, this remains steady and I can work perfectly in
it. However, when i close the file and open it again, it appears the message
“It's not possible you open fileâ€.

The Excell tries to recover the data, but it does not obtain it because “the
damages had been very extensiveâ€. The same thing happens if i try to copy
sheet’s of the file to the file itself.
Somebody helps me please.

Thanks,
Paulo Martins

 

[Stephane Quenson]

If you have links to external spredsheets, then each of these external
worksheets are considered as one worksheet in your document, even if not
visible. This is how Excel can still display informationif even if the links
are not updated, by the way.

So you may have reached the limit of worksheets per document, which is 255
if I am not mistaken.

Stephane.

 

[Paulo Martins]

Many thanks Stephane, but i don't have any links to external spredsheets.
This spredsheet is the origin of others spredsheets.

Paulo Martins

 

[Gord Dibben]

255 sheets is the limit for a new workbook.

More can be added after the workbook is open if your resources will allow.


Gord Dibben MS Excel MVP

 

[Paulo Martins]

Many thanks Gord, but i didn´t reach the limit of my workbook because i have
only 160 sheets.
By the way, how can i add more than 255 sheets to a workbook?

Paulo Martins

 

[Nuno Duarte]

Hi there,
has a friend of Paulo Martins and as a Computer Tech maybe I can Try and
explain the problem in a better way.
So, he has an Excel 2003 Workbook with 160 spreadsheets. If you take one of
those spreadsheets and copy it to a new Workbook and the you put it again in
the origin Workbook, after you close it it won't open again.
If you try and select an external spreadsheet by cliking in the upper left
corner (selecting all cells) and paste it into an existing sheet, after you
save and close it won't open again.

I've already tryied everything I can think of... the result is allways the
same, it works fine until you close the file and try to re-open it.

Hope that someone has any ideas!!!

cya,

 

[squenson]

Few questions to try focusing where the issue is:

1. If you work with a copy of the original document with much less than 160
sheets (let's say 10) and you save and re-open, do you have the same problem?

2. Is there any cell that may contain more than 1,000 characters in it?

3. Are you using some features like autofilter, in-cell validation or
external links, or anything else that could be counted as "objects"? If you
would estimate the total number of these objects (one autofilter cell = 1
object) in the original document, what number would you get?

4. Can you post a streamlined version of the document without any
confidential information at http://www.mediafire.com (free and no
registration required), and post the link here? Or send it to my private e-
mail address (remove all the numbers from it, this is a spam trap):
q77_stephane "at" hot2mail "dot" com

 

[Nuno Duarte]

Thank you for your help squenson.

I've just sent you an e-mail wih the problematic file.

 

[Gord Dibben]

Under Tools>Options>General "Sheets in a new workbook" you can set to a max of
255 sheets.

After the new workbook with 255 sheets is created, you can add more sheets by
Insert>Worksheet.


Gord

 

[Paulo Martins]

Thanks for the tip Gord.

Paulo Martins

Under Tools>Options>General "Sheets in a new workbook" you can set to a max of
255 sheets.

After the new workbook with 255 sheets is created, you can add more sheets by
Insert>Worksheet.


Gord

 

[squenson via OfficeKB.com]

The file is really big (45 MB), contains more than 80,000 formulas, but I
managed to insert, copy and paste new sheets from within and from outside,
only a couple of times I reached an error message saying that Excel could not
perform the copy operation because of a memory problem. I recently got an
error like "too severe to repair", and I copied all the sheets (5, and much
smaller file, about 3 MB) to a new file. As I had texts larger than 255
characters, I did it sheet by sheet, copying the entire cell range. I
understand that this may be tedious when you have 160 of them, but since then,
I am having no problem, so may be something is wrong in one sheet and the
copy process could fix it.

For me, you have or are close to reach some Excel limits, so I would
recommend that you split the file into several files, one per person for
example. You may also consider an upgrade to Excel2007, you can download it
for 60 days free of charge, this gives you ample time to see whether the
"bug" is gone with the new release.