Excel was able to open the file by repairing or removing the unreadable content.

[soxlug]

This has happened a couple of times where I open a locally stored spreadsheet and get a warning that reads:

Excel was able to open the file by repairing or removing the unreadable content.

Removed records: shared formula from /xl/worksheets/sheet3.xml part
Removed records: formula from /xl/calcChain.xml part (calculation properties)

click to view log file listing repairs: "local path to an xml file"

the xml file then basically shows the removed records that are shown above. I am not sure what is causing this issue has anyone seen this and have an idea as to why this warning appears.

 

[Yard]

Hi,

You mean you don't find this feature useful?

If you have very important data in a sheet and Excel corrupts it, then at least the recovery feature gives you a chance to get the actual data back. The cost of that could be loss of conditional formatting, pivot tables, charts, etc etc etc.

If you are fortunate enough to have backups of your files then I would recommend rolling back to a version prior to the crash/problem.

If not, I would recommend some deep breaths and proceed with caution - I have found that by removing these xml file parts Excel can make the file forever unusable.

Maybe someone else has a more positive experience....???

 

[soxlug]

I definately appreciate your response, however it does not help me get to what the cause is. The spreadsheet itself was created basically as a template and has zero data on it, just shows me where to put what at this point. So I am not in deep but would like to know why this comes about.

 

[Yard]

I wish I knew. I just had the same thing happen again today. I opened a file I use day in, day out and it now has unreadable content.

Good luck and post back if you narrow down a cause (I suspect it will be one of a myriad).

 

[Yard]

Don't know why I'm posting this, but the file I was working on all day yesterday and saved without a problem at 16:30 yesterday has just opened with a bang. The "repair" has decimated the Table functionality and has removed some Autofilters.

This points me towards the Table functionality as a main suspect. I don't believe this has been properly designed or fully tested and sorted out by Microsoft, and in my new version I will be abandoning this and reverting to good old-fashioned formulas.

Is it too early for a gin & tonic? I need something to relax me after this.

Yard

 

[xenou]

Thanks Yard...That's worth keeping in mind. Alex.

 

[JMH022]

I also had this problem (Excel 2007) when the Data Connections were lost on underlying query tables. Just my 2-cents.

HTH

 

[Rachaelsqa]

I have had 3 unreadable content problems - 1 resulting in corruption , the other in repair , and the last in data save only .

1. Repaired without data loss = The xl/xalChain.xml problem was repaired by excel with the content was intact , with the loss of formatting only to the cells with the sumproduct formulas in them , even those this is a large extensive workbook with lots of macros. These were my only losses with this error that repaired itself.

Like you, this file was a template file. The calChain corruption, from what I can tell , occured during my beta testing of this file in which i imported data programically from a previous version of the template i was using.

To keep the file intact ( and it works now) , I cleared everything from the cells that had the sumproduct formulas ,and reentered them , and referenced them. ( they contained references to named ranges. ). So far so good.

2. Data loss resulted in complete corruption.
I received an unreadable content error, excel tried to repair it , etc. The repair failed and I lost everything. Luckily I had back up version and located the problem .

The problem was with a named range I was using within a table that over time was referenced to the wrong source. I deleted the named range , reentered it , now it works like a champ.

Named references can easily get screwed up if you import and export the information to and from other workbooks programically via copy/cut paste and do not clean ( ie apply and or reappy the reference to , and scope) the workbooks named range reference. OH I learned this the hard way.

3. The 3rd had to do with conditional formatting used within a table. The unreadable content was saved by giving me data only , loss of formats, data validations ,etc.

What I have found is if you move info around in a table via cut and paste with the table itself ,or from another source for reason i have yet to figure out the applied conditional formatting can get all screwy and refernce all kinds of cells and ofter re-references itself.

Once i cleaned this up by referencing the table itself instead of cells ( it will default back to a cell address but it does seem to remember the fact it should reference the table) . I did not have a problem again.


1. check named ranges
2. check formulas within a table , and the references
3. check table conditional formatting settings


Hope my trial and errors help you in someway.

take care,
Rachael

 

[Geoff Taylor]

I had this error last week. Whatever the cause, the practical effect was that I lost about 1 in 30 of all formulas in my model, from simple SUMs to HLOOKUPs. The formulas were replaced with values, so it was not immediately apparent that there was a problem. The effect was totally random which meant a trawl through the model looking for numeric constants where formulas should be.

This was a one-off piece of work being done in a hurry (aren't they always), so I could have done without the extra 2 hours spent fixing it. It's also highly embarrassing when you submit the work to a higher power only to discover that it's riddled with errors.

Reading this thread, it appears that moving table columns around may be a specific cause. I rearranged some columns prior to my "crash" so that gives a little more weight to the theory.

 

[successone]

We finally got this message to go away!!! In our case, in Excel 2010, it was due to links to a file that hasn't existed for several years. Here is how we fixed it.

When you receive the message error, click Yes.

When the repair message comes up, click Close.

File, Option, Trust Center, Trust Center Settings, External Content, Changed from Prompt to Disable on both settings. Click Ok.

Save workbook and the error was gone when reopened it. Of course, we will probably have an issue if they ever want to link to another workbook in the future, but I spent enough time figuring this out!

Hope this helps someone out there.