Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

Summary: Excel repairs my file, tells me what was removed, I go into the xml/zip structure to investigate, and I cannot figure out what was changed. Does anybody know what I can do to better understand what Excel changed? Is it futile to try to determine? It feels like this should be possible and like I'm almost there...

Details:

When I open a file that I have renamed unnamed.xlsm, I receive the following notice: "Excel found unreadable content in 'unnamed.xlsm'. Do you want to recover the contents of this workbook? If you trust the sounce of this workbook, click Yes."

I know the file is safe, I click yes, and I receive a message that "Excel was able to open the file by repairing or removing the unreadable content." It provides the following summary, but also provides an xml file which seems to contain the same content so I did not show it. Summary:

Removed Records: Shared formula from /xl/worksheets/sheet3.xml part
Removed Records: Formula from /xl/calcChain.xml part (Calculation properties)

In order to determine issue, I have created a copy of the offending file, renamed it to have a '.zip' ending, opened up the files that Excel says it modified (sheet3), and perused the xml content, but this was not informative. I tried saving the repaired file and doing a simple diff on the xml for sheet3, but there are many changes and this is not informative either. I did the same thing for calcChain.xml and this was more useful. After saving the displayed xml with line breaks in text format, it was easy to identify the items that have been removed, but now I want to make sense of them. Perhaps they give clues of what happened to shee3. The following comparison is long, but I don't know if the entire train of differences is relevant.

FILE COMPARISON
Produced: 1-7-2011 2:42:26 PM

Mode:  Just Differences

Left file: u:\My Documents\[redacted]\calcChain_orig.xml
Right file: u:\My Documents\[redacted]\calcChain_rep.xml
812    <c r="H18" i="8" />  <> 812   <c r="N2" i="8" />
814    <c r="G18" />        +-
816    <c r="D19" />        +-
818    <c r="F19" />        +-
820    <c r="E18" />        +-
822    <c r="N2" i="8" />   +-
824    <c r="H18" />        +-
                            -+ 820   <c r="H15" />
                               821   <c r="H13" />
                               822   <c r="O19" />
                               823   <c r="O17" />
                               824   <c r="O15" />
                               825   <c r="M19" />
                               826   <c r="M17" />
                               827   <c r="M15" />
                               828   <c r="M13" />
                               829   <c r="J19" />
                               830   <c r="J17" />
                               831   <c r="J15" />
                               832   <c r="J13" />
                               833   <c r="O14" />
                               834   <c r="H18" i="8" />
                               835   <c r="G18" />
                               836   <c r="D19" i="5" />
                               837   <c r="F19" />
                               838   <c r="E18" i="8" />
                               839   <c r="H18" i="9" />
827    <c r="H15" />        +-
829    <c r="H13" />        +-
831    <c r="O19" />        +-
833    <c r="O17" />        +-
835    <c r="O15" />        +-
837    <c r="M19" />        +-
839    <c r="M17" />        +-
841    <c r="M15" />        +-
843    <c r="M13" />        +-
845    <c r="J19" />        +-
847    <c r="J17" />        +-
849    <c r="J15" />        +-
851    <c r="J13" />        +-
853    <c r="O14" />        +-
1209   <c r="H48" />        +-
1210   <c r="H62" />
share|improve this question

2 Answers 2

up vote 2 down vote accepted

The only real diffs after the set was sorted:

1,2c1,2
< <c r="D19" />
< <c r="E18" />
---
> <c r="D19" i="5" />
> <c r="E18" i="8" />
7d6
< <c r="H18" />
9,10c8
< <c r="H48" />
< <c r="H62" />
---
> <c r="H18" i="9" />

Looks like H48 and H62 might have been the corrupted or missing ones, the removal of which necessitated adding explicit numbers into D19, E19, and H18, whereas implicit values had been fine before. Non-Microsoft software that edits OOXML files will often do this; smarter software just deletes the calcChain file entirely and forces Excel to recreate it.

A regular diff software is always going to be a total fail for XML, use an XML diff instead. There's a large number of GUI and command-line ones, you might be able to find the elusive difference in sheet3.xml that way.

share|improve this answer
    
Dead on! Those were the affected cells. How were you able to read this out of the info above? Did you feed it into an XML diff tool? I think I'll download one... –  sage Jan 8 '11 at 1:04
    
I just used a box copy/paste (most text editors, like Editplus and Notepad++, have it when you hold down Alt) to copy each side into different files, sort them, remove whitespace, and then diffed the two files. –  SilverbackNet Jan 8 '11 at 1:50
  1. Go to your Control Panel
  2. Open Add or Remove Programs
  3. Look for Microsoft Office
  4. Hit Change
  5. Select Add or Remove Features
  6. Unfold Office Shared Features
  7. Set Visual Basic for Applications to run from your computer.
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.