I received a "Repair" error in a Excel 2007 Model that was originally in Excel 2003 that I just Saved As.

The repair list a bunch of sheets and and has an xml extension. It looking further it turns out the "Repairs" are to overwrite a formula with its value. We found a Hot Fix from 2008 (kb954894) that reference this but it doesn't seem to completely handle the issue.

Does anyone have any insight on what causes the formulas to be overwritten, how to find the ones that were and how to prevent this in the future?

We were able to get the "error" to happen again. See below:

"Excel found unreadable content in 'File.xls'. Do you want to recover the contents of this workbook? If you trus the source of this workbook, click Yes."

It also has an log of what was "repaired". See below:

<?xml version="1.0" encoding="UTF-8" standalone="yes" ?>
- <recoveryLog xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
<logFileName>error016600_01.xml</logFileName>
<summary>Errors were detected in file 'File.xlsx'</summary>
- <removedRecords summary="Following is a list of removed records:">
<removedRecord>Removed Records: Shared formula from /xl/worksheets/sheet1.xml part</removedRecord>
<removedRecord>Removed Records: Shared formula from /xl/worksheets/sheet81.xml part</removedRecord>
<removedRecord>Removed Records: Shared formula from /xl/worksheets/sheet99.xml part</removedRecord>
<removedRecord>Removed Records: Formula from /xl/calcChain.xml part (Calculation properties)</removedRecord>
</removedRecords>
</recoveryLog>

So far this is what we have figured out. Anything else anyone has come accross would still be appreciated as it doesn't look like a problem that is going away.

The "Repaired" Sheets listed in the "Log" are in literal order and not by actual sheet name when viewed in VB Editor.

As mentioned, the repairs overwrite the formulas with a hard code value. To find these we select the areas that should only be formulas and then use "Go To Constants" (Ctrl-G > Special > Constants) which isn't very creative but has been helping.

Beyond this we are still lost as to why it happens and how to prevent it.

Can you give us an example of one of the formulas which was overwritten by a constant? The log says the formulas are shared, so I assume they are array formulas which span several cells?

Below are some of the Formulas that were overwritten.

=DATE(YEAR(AC7),MONTH(AC7)+1,1) where AC7 is hard coded date
=IF(Q21=0,1,AC21/Q21) references a cell which references data from another sheet
=AD210 references a cell which references data from another sheet

All the data is contained in the Workbook with no external links but I am copying and pasting values from other Workbooks into this one.

Thank you!

We are also running a Full Calc Rebuild using VBA on the file. This could have something to do with it but it doesn't always happen so it could just be a coincedence.

If the file does not contain sensitive data, would you zip and attach the .xls (containing all formulas) to the thread? I could then try opening and saving it as .xlsx in Excel 2007 and Excel 2010 to see if I can get the same error as you?

The file does contain sensitive data so I wouldn't be able to add it. It is also difficult to "replicate" as it doesn't always happen. It does seem to happen more often for one of my team members then for me but I am not sure what the difference is.

I appreciate the interest. Thank you.

Hi Rich

I remember reading a thread on another forum where a user experienced this because they had undocked/redocked their laptop while the file was open. This shows that there could be "non Excel" factors at play which makes problem shooting it (especially from where I'm sitting!) quite tricky. Until a more distinct pattern emerges, the only suggestion I have is to ensure that all users have the latest Office updates installed (which it sounds like you have).

The formulas you posted look very straightforward (no ATP functions etc), so I can't see a problem with them. You were saving the file as an xlsx which implies to me that there is no VBA in the workbook - so again, this can't be the cause of the issue.

Thank you Colin! I was hoping it wasn't something like that but it does seem to be. I appreciate you assitance!

While continuing with this issue, one pattern has emerged. It seems that Moving and/or Copying Worksheets in and out of the Workbook increases that chances of the file Corrupting.

I have managed this by using copy and paste or creating from scratch when I need to incorporate a new worksheet.