Excel Worksheet and backups suddenly get Unreadable Content Message

Simply amazing! I have 27 sheets, all with picture comments, and most with well over 100 records each. This little tidbit fixed every one in seconds! Needless to say, I'll keep this little script handy from now on. I can't even imagine how long it would have taken to edit each one...

Hopefully this will get taken care of seeing as how it's been happening, apparently, for well over 2 years.

Thank you!

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

I've got this prob too with embedded photo in cell comment (Office Plus 2010 Excel). Something happens, the file is unreadable, recovery and then comments with photo are removed.  I tried the OpenOffice routine and got some photos back.  I want to try and run this, but do not know how to do it.  Any baby step instructions are greatly appreciated!! Thanks!

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

OMG!  Thank you for writing/posting this vbs.  I can't tell you how much work this saved me.  Awesome.

Easy solution that has worked for me many times with this unreadable content error.

Open the file in OpenOffice calc.

Save as a new name and then open it in Excel.

Often its back to normal.

Hi AceGreen

did it work?

I have to deal with this theme again just in this week because of an other problems in my repair-procedure. I tried again and found out, that reproducable a sheet gets corrupt, if you delete a line with a comment (not every line, but special ones... ) In my case the line was the last, but the comment number was not the last.  The comments seem to be numbered automatically after their creation.

I found out, that the picture-renaming-routine I posted before was not working in this case.

In my bigger repair-procedure (which does much more) I isolated that one repair was sufficient to stop corrupting my testfile.

In this procedure I change the top position of the comment shape. It is not enough to set shape.name = shape.name (I though maybe any writing into the comments may repair them, it does not. ) But this one seems to work (again in the Workbook_close event)

If you try this, I would be glad for a comment (working/not working).

Thx Thomas

    Dim objcomment As Comment

    ' run thru all comments

    For Each objcomment In ActiveSheet.Comments

        With objcomment.shape

            With .Shape

                .top = objcomment.Parent.top

            End With

            .Visible = False

        End With

    Next

The comments loose their top position, if you make them visible by code. e.g.

ActiveSheet.Cells(ActiveCell.Row, kommentarspalte).Comment.Visible = True

so my repair function is necessary anyway. The position onmouseOver is ok.