Excel is unable to read file

[Logan.M]

I have a fairly large problem, I work on Excel files quite large (about 90-100 tabs) and approximately 100 lines and a 50 subject column for 80% of the tabs. In these tabs there are many formulas and named zone. There are also many vba behind.

I therefore find myself with a file that does a twenty MB.

My problem is that, from a number of data when I save and reopen my file, Excel does the following error message.

"Excel found unreadable content in 'myFile.xls. Do you want to retrieve the contents of this workbook?

I tried to click on yes, but I only get the data. I lose all my formulas, formatting and all my vba modules.

I have the impression that it is related to the amount of data, since the file works fine until it reaches at around 20MB.

The problem is not from a specific location because it is planted after various things such as adding a tab (filled), or creation of new zones named (by approximately 10 to 60 tabs to 70 tabs) or adding a formula in line with 80% of the tabs.

The common feature of all these crash is the size of the file, so I think my file is too big, but I do not know what it is from.

[Praetor]

Hello,

There are several possible reasons for this kind of problem:

insertion of images, graphics and so on.
using variety of tools for formatting: background color, text color, borders and so on.

If this is not your case, you need to face the facts:

-c 'is the number of leaves and especially of forms (including limitation in principle, for some as for others, depends only on the size of available memory).

You've probably complex formulas referring to cells in other worksheets and maybe they also contain formulas.

I met this case with some individuals but also in a large company and before they could no longer open the file, I have found a solution:

For maximum formulas, see all, do the processing in VBA and assign values.

[Katty]

I also encountered this problem with a very large file (a long time ago - I can not remember the version of Excel).

I came to the conclusion that Excel (like Access) managed internally intermediate states that backed up along with the changes.

In Access it is necessary to compact the base regularly so that its size exploded not (given a base of 20MB up to 500 MB!) --

Excel, anything except the kind that loses repair data at one time or another.

The only solution I have found at that time was to reconstruct the final workbook in one step to the "clean"

[Logan.M]

To answer the various questions, I use little or no images, no graphics.

For against, I have a lot of formulas that rely on named areas or cells with formulas.

For formatting, indeed there are many colors in the tables but I did test by removing the color, and I have gained almost nothing.

I also came to the conclusion that eases my things. I started by removing all the white lines too. And I think the next step will be to redo my formulas in vba.

For the reconstruction, I am not very motivated to do so because I have a lot of zone named, and it should be all redone.

[Praetor]

If you can do this treatment you will see that in VBA based on the number of formulas replaced the file size may be considerably reduced.

One thing maybe to check, this is not the most important, but it is cheap:

by selecting the cell "A1" on your sheets you press Ctrl + End.