Unreadable content found on Opening

[Trims]

Hi

I have a problem which has just started occurring in a spreadsheet I have been using for a while with no problems.

Windows 7, MS Office Pro 2010. file saved as xlsm (macro enabled) it has all been working, saving and opening OK, then I started getting this alert when opening.

Excel found unreadable content in ' Sept Workbook.xlsm'. Do you want to recover the contents of this workbook? If you trust the source of this workbook, click Yes.


I click yes and it does its stuff and offers me to view repairs made. :-

<?xml version="1.0" encoding="UTF-8" standalone="true"?>
-<recoveryLog xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">

<logFileName>error060240_02.xml</logFileName>
<summary>Errors were detected in file 'C:\Users\Gordon\Documents\Forex Weekly Analysis\Sept Workbook.xlsm'</summary>-

<repairedRecords summary="Following is a list of repairs:"><repairedRecord>Repaired Records: External formula reference from /xl/externalLinks/externalLink1.xml part (Cached values from external formula reference)</repairedRecord>

<repairedRecord>Repaired Records: External formula reference from /xl/externalLinks/externalLink2.xml part (Cached values from external formula reference)</repairedRecord>

<repairedRecord>Repaired Records: External formula reference from /xl/externalLinks/externalLink4.xml part (Cached values from external formula reference)</repairedRecord>

<repairedRecord>Repaired Records: External formula reference from /xl/externalLinks/externalLink5.xml part (Cached values from external formula reference)</repairedRecord></repairedRecords></recoveryLog>

Spread sheet works OK, except some minor conditional formatting changes, it comes up as Sept Workbook (repaired) and will not allow mine to save it directly it makes me save as, which I have done with new name and overwritten the old one, when I shut it down and reopen it still does the same.

This spreadsheet is quite complex and has DDE sever input live, but has always worked OK. I did have a problem with SPX500 index and other indexes on opening of new day, the formulas was changed, which was kindly solve at this link

http://www.excelforum.com/excel-form...59#post2914259 I think this is when the problems started although the solution worked well, VBA has caused a problem. For now I have removed it and removed the problem inputs so it should not still be causing a problem.

I have some old backup files when I open these they work fine as they did before, but when I save them and exit and reopen the same thing about unreadable content reappears. However when I use a backup on my laptop with Windows Vista and Excel 2007 it all works OK, including saving as Macro enabled.

So I conclude something might be happening with the save.

If I save it as a workbook without macros enabled same thing (unreadable content) but if I save it as 2003 workbook file it opens fine again, but some of the conditional formatting does not work the same.

Although the spreadsheet still works OK, it's not working as intended, any help to solve this annoying problem would be very much appreciated. I have Googled this to death and found lots of similar things but nothing so far has worked.

Thanks

[Trims]

Anyone have any ideas? At all, even something to point me in the right direction.

[Trims]

OK after a lot of searching and piecing together lots of bits and pieces I found a solution of sorts.

For some reason external links which I could not find where causing the problem. I renamed the file from *.xlsx to *.zip.
Opened it in unzip and accessed the [content_types] .XML file with Notepad (or a text editor) and found the related external links, matched to the repairs previously carried out, which I then deleted manually; I did this with a backed up version.

PartName="/xl/worksheets/sheet7.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml"/><Override PartName="/xl/externalLinks/externalLink1.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.externalLink+xml"/><Override PartName="/xl/externalLinks/externalLink2.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.externalLink+xml"/><Override PartName="/xl/externalLinks/externalLink3.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.externalLink+xml"/><Override PartName="/xl/externalLinks/externalLink4.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.externalLink+xml"/><Override PartName="/xl/externalLinks/externalLink5.xml"

I then saved it and renamed it back to *.xlsx. On opening the file again it did show the unread content but it was talking about some formula which it repaired. It opened and all seemed to be OK. I saved it as a new *.xlsx and closed it on reopening it opened normally.

Now I am no expert but it has got rid of a very irritating pop up on opening and allowed me to save the file normally instead of a save as only. I can re applied the conditional formatting previously lost by the repair and once I tidied up a couple of other things it is all working again .

Why? Not sure but I think it might have been something to do with cutting, pasting and amalgamating sheets from other work books, I am not sure Excel handles this well. Also I had recently added some VBA macros which may not of helped. Anyway it works now, so thought I would write what I did in case anyone else came across this problem as goggling it did not find any real solutions.

Anyone who can throw some light on this issue, advice would be welcome. Thanks

[cubsfan@everestkc.ne]

Brilliant! Worked a treat for me.

Thanks so much.

[konami]

hi, i had the same problem with you. but i'm not quite well understand. please have a look

<?xml version="1.0" encoding="UTF-8" standalone="yes" ?>
- <recoveryLog xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
<logFileName>error030840_01.xml</logFileName>
<summary>Errors were detected in file 'C:\MyFile\Cost.xlsx'</summary>
- <removedRecords summary="Following is a list of removed records:">
<removedRecord>Removed Records: Style from /xl/styles.xml part (Styles)</removedRecord>
</removedRecords>
- <repairedRecords summary="Following is a list of repairs:">
<repairedRecord>Repaired Records: Format from /xl/styles.xml part (Styles)</repairedRecord>
</repairedRecords>
</recoveryLog>


and below line is [Content_Types].xml, it content :

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<Types xmlns="http://schemas.openxmlformats.org/package/2006/content-types"><Default Extension="bin" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.printerSettings"/><Override PartName="/xl/worksheets/sheet9.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml"/><Override PartName="/xl/theme/theme1.xml" ContentType="application/vnd.openxmlformats-officedocument.theme+xml"/><Override PartName="/xl/styles.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.styles+xml"/><Override PartName="/xl/worksheets/sheet6.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml"/><Override PartName="/xl/worksheets/sheet7.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml"/><Override PartName="/xl/worksheets/sheet8.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml"/><Override PartName="/xl/worksheets/sheet11.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml"/><Default Extension="rels" ContentType="application/vnd.openxmlformats-package.relationships+xml"/><Default Extension="xml" ContentType="application/xml"/><Override PartName="/xl/workbook.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet.main+xml"/><Override PartName="/xl/worksheets/sheet4.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml"/><Override PartName="/xl/worksheets/sheet5.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml"/><Override PartName="/xl/worksheets/sheet10.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml"/><Override PartName="/docProps/app.xml" ContentType="application/vnd.openxmlformats-officedocument.extended-properties+xml"/><Override PartName="/xl/worksheets/sheet2.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml"/><Override PartName="/xl/worksheets/sheet3.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml"/><Override PartName="/xl/worksheets/sheet1.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml"/><Override PartName="/xl/calcChain.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.calcChain+xml"/><Override PartName="/xl/sharedStrings.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.sharedStrings+xml"/><Override PartName="/docProps/core.xml" ContentType="application/vnd.openxmlformats-package.core-properties+xml"/></Types>

to many words. which statement should i delete it ?
your help are much appreciated.

[arlu1201]

konami,

Unfortunately you need to post your question in a new thread, it's against the forum rules to post a question in the thread of another user. If you create your own thread, any advice will be tailored to your situation so you should include a description of what you've done and are trying to do. Also, if you feel that this thread is particularly relevant to what you are trying to do, you can surely include a link to it in your new thread.

[konami]

oh, sorry. I move my question to my thread. thank you

[A40infobahn]

I got the same problem as Trims, several occurences in similar files. I see and understand his solution, (open in Zip and edit XML files in Notepad)

BUt I got thinking both TRims and I had problems with xlsm files with conditional formatting. I examined my file and discovered that the range with the conditional formatting was non continuous, so I now have 10 discrete Conditional formatting rules. Consolidated the Conditional formatting to a single rule and deleted the old ones, no I only have one Conditional Formatting rule for the workbook, and the problem goes away. I deduce this issue is due (in my case) to too much Conditional Formatting, and can be avoided by simplifying the workbook. (possibly some of the CF formulae contradicted one another?) Hope this helps

A40infobahn

[sumdumgai]

For those who still might be having this problem, I fixed it by writing a 6 line VBA program that makes a copy of each sheet in the corrupted workbook, moves that copy to a new workbook and renames that sheet in the new workbook same as copied sheet. All of the names and links are, of course, copied. When all sheets have been copied and moved, on the new workbook, the links are changed to their original workbooks. Rename the new workbook to original and you're done.

[baaggii]

The corrupted file opens after waiting some hours, at last.
Saved to another folder, and it opens without problems. Deleted unused vba procedures, buttons. and now the file is good.

Earlier,
Tried, what Trims did, but didn't rename, just opened .xlsm file with 7zip, opened content file in the zip application, deleted some external links, saved, and updated the file.
Opens immediately, shows some repairs, but external paths of the formulas have all changed. It was not a good idea.

For me, it seems to be, better wait until excel opens, after save to another folder. Delete unused conditional formatting and vba.

That seems to be solcing the problem.

Good luck guys.