Excel Worksheet and backups suddenly get Unreadable Content Message

Thanks dlauzon that was exactly what I needed. I created a vbs to clean up my VML file since it was pretty extensive, and here it is in case it helps anyone else:

' drag and drop the .vml file onto this script, new file is saved to the same location with a Fixed_ prefix

' bring in drag and dropped file

Set objArgs = WScript.Arguments

Set oFSO = CreateObject("Scripting.FileSystemObject")

Set oFSO2 = CreateObject("Scripting.FileSystemObject")

sFile = objArgs(0)

Set fileinfo = oFSO.GetFile(sFile)

' create new file

sFile2 = fileinfo.parentFolder & "\Fixed_" & fileInfo.name

If oFSO.FileExists(sFile) Then

 Set oFile2 = oFSO2.CreateTextFile(sFile2)

 Set oFile = oFSO.OpenTextFile(sFile, 1)

  Do While Not oFile.AtEndOfStream

   ' read through input file one line at a time

   sText = oFile.ReadLine

   IStartPos = 0

   iStartPos = InStr(sText, "o:relid=")

   ' if an o:relid is found we do special processing

   If iStartPos > 0 Then

    ' keep appending lines from the input file until we reach o:title

    iTitlePos = InStr(sText, "o:title=")

    While iTitlePos = 0

     sText = sText & oFile.ReadLine

     iTitlePos = InStr(sText, "o:title=")

    Wend

  

    ' find the LAST o:relid if there are more than one

    iNextPos = InStrRev(sText, "o:relid=")

    If iNextPos > 0 and iNextPos > iStartPos Then

     ' this is the last o:relid

     sItem = Mid(sText, iNextPos, iTitlePos - iNextPos)

     ' replace the multiple o:relid with only the last one

     sText = Left(sText, iStartPos - 1) & sItem & Mid(sText, iTitlePos)

    End If

   End If

   oFile2.WriteLine(sText)

  Loop

 oFile.Close

 WScript.Echo "Done"

End If

Could you please explain what you mean by "drag and drop the .vml file onto this script"  I extracted the vml file but I'm stuck on this step. Thank you.

I had the same problem using Excel 2013 on an .xlsx file with images inside comments.

I was able to get the document back to a working state, and at the same time see where the corruption is.

Here's what worked for me (summary: remove a couple of bogus tags in an embedded text file):

1. take a backup of the file (just in case)
2. open the file and accept Excel's tentative to recover the file
3. note the full path of the "removed part" file, e.g. "/xl/drawings/vmlDrawing2.vml"
4. close the file without saving
5. rename the file to end with .zip (e.g. myExcelFile.xlsx.zip)
6. open the zip file, it shows as a folder hierarchy (if you're curious to see where your images are stored, I found them fully readable under the following folder in the zip file: \xl\media\ )
7. extract the problematic file found in step 3, e.g. "/xl/drawings/vmlDrawing2.vml"
8. open it with Notepad
9. if you want to see what is wrong with this file, you can copy and paste its whole text content to an online XML syntax checker (e.g. http://www.w3schools.com/dom/dom_validate.asp).  Mine showed duplicate attributes on a given line (once you correct a line, you can re-paste your corrected text to find the next error, and so on).
10. Here's the bug I had: all the v:fill tags had multiple o:relid attributes, even a working file has multiple, and it shouldn't, but in the case of the broken file, one of those attributes is different from the others, e.g.: v:fill o:relid="rId3" o:relid="rId3" o:relid="rId3" o:relid="rId3" o:relid="rId3"  o:relid="rId2"  <--- note the "rId2" instead of ="rId3" like the rest of the line
11. for each v:fill tag, remove all but the last o:relid (in the previous example, that part of the line would now be v:fill o:relid="rId2" )
12. once you have verified this for each v:fill, the XML of the file should be ok (step 9), save the file and put it back in the zip (overwriting the original one)
13. rename the Excel file to its original name (e.g. myExcelFile.xlsx) by removing the .zip extension added in step 5
14. open the file with Excel, everything is back to normal

The error is probably due to changes that force renumbering of the existing images such as removing a line on which there is a comment with a picture on it (if it's not the last picture added) - I can reproduce the bug every time I remove one such line.  We can see that just the last entry ("rId2") is corrected, the rest stay the same ("rId3"), and Excel can't cope with those different values for the same tag.  To Microsoft: preventing multiple occurrences of the same attribute on a given tag should be the initial goal to solve this issue.

This works.  However, for me, the XML syntax checker always indicated a duplicate attribute (for both files that work and don't work).  I instead just followed 10 - 14 and my spreadsheet is working again as it should.  Thanks for documenting this workaround!

Hi,

It's the other way around, the vml file had to be extracted and dropped unto the vbs and the corrected file reput back in the .zip.

You can try the 14 manual steps instead of using the vbs, they are quite detailed and should also work.

Hi guys

I have been tying to do this. The images are all there in the media folder. When you say 'drop the correctvml.vbs on the vml file in the zip, all that happens is the vbs file gets compressed and added to zip file. So then I have the vml and the vbs files in the folder and the spreedsheet still says 'unreadable content' etc. when I zip it back to xlxs and open it..... Please help, I've been trying to do this for days!

I had the same problem using Excel 2013 on an .xlsx file with images inside comments.

I was able to get the document back to a working state, and at the same time see where the corruption is.

Here's what worked for me (summary: remove a couple of bogus tags in an embedded text file):

1. take a backup of the file (just in case)
2. open the file and accept Excel's tentative to recover the file
3. note the full path of the "removed part" file, e.g. "/xl/drawings/vmlDrawing2.vml"
4. close the file without saving
5. rename the file to end with .zip (e.g. myExcelFile.xlsx.zip)
6. open the zip file, it shows as a folder hierarchy (if you're curious to see where your images are stored, I found them fully readable under the following folder in the zip file: \xl\media\ )
7. extract the problematic file found in step 3, e.g. "/xl/drawings/vmlDrawing2.vml"
8. open it with Notepad
9. if you want to see what is wrong with this file, you can copy and paste its whole text content to an online XML syntax checker (e.g. http://www.w3schools.com/dom/dom_validate.asp).  Mine showed duplicate attributes on a given line (once you correct a line, you can re-paste your corrected text to find the next error, and so on).
10. Here's the bug I had: all the v:fill tags had multiple o:relid attributes, even a working file has multiple, and it shouldn't, but in the case of the broken file, one of those attributes is different from the others, e.g.: v:fill o:relid="rId3" o:relid="rId3" o:relid="rId3" o:relid="rId3" o:relid="rId3"  o:relid="rId2"  <--- note the "rId2" instead of ="rId3" like the rest of the line
11. for each v:fill tag, remove all but the last o:relid (in the previous example, that part of the line would now be v:fill o:relid="rId2" )
12. once you have verified this for each v:fill, the XML of the file should be ok (step 9), save the file and put it back in the zip (overwriting the original one)
13. rename the Excel file to its original name (e.g. myExcelFile.xlsx) by removing the .zip extension added in step 5
14. open the file with Excel, everything is back to normal

The error is probably due to changes that force renumbering of the existing images such as removing a line on which there is a comment with a picture on it (if it's not the last picture added) - I can reproduce the bug every time I remove one such line.  We can see that just the last entry ("rId2") is corrected, the rest stay the same ("rId3"), and Excel can't cope with those different values for the same tag.  To Microsoft: preventing multiple occurrences of the same attribute on a given tag should be the initial goal to solve this issue.

Thank you!

I had the same problem, with an error in the Drawing1.xml file. It was a large file with a lot of pictures and drawings. There were no syntax errors in the XML file, but I could however locate the section of code where the problems were. After deleting it, I got 95% of my file back. This saved me hours of work rebuilding the file!