Excel Worksheet and backups suddenly get Unreadable Content Message

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.

Hmm.  Interesting.  When I copy a newly created comment picture from File1 to a brand new File2, there is only one picture object in File2, there is no problem in File2 upon save and reopen.  If I save File1 after creating the picture comment, reopen File1 and then copy this same image comment to brand new File3, that File3 is corrupted upon Save/Reopen, yet there is only one image object in that entire file.

The source File1 does not experience any problems, but any copying of a previously saved comment picture to another tab of File1 or to a totally different file corrupts the target file.  This implies the picture data being copied to the target is bad as soon as File1 has a Save operation after the picture data is created, but is OK before any Save.

Not sure from your remarks whether Excel is confused with the comment text having the same name as the picture name.  In my case my comment text is the name of the Actor, and the background picture of the comment is from a file <actor name>.jpg.  In fact I created a VBA subroutine and associated hot key which, based upon invocation of the hot key, it looks up the actor name from a different field, creates a new comment with that actor name as comment text, and fill in the comment picture from the file of that same name (which I suppose assign that name to the picture object).  Which is to say my <comment text> = <picture name> for the same comment.

I will try your suggestion tomorrow on changing one or more of above parameters to see if problem goes away for Excel 2010/2013 format files.  You Exit routine to run for bulk adjusting the problem parameter values (image object name) is a good idea if that is causing the problem, or perhaps in my case add some extra steps in my comment create VBA to modify the newly created object name immediately after each create.  I will let you know how I make out.  Many thanks for pointing out to me the likely cause of this corruption situation.

I dealt with that problem two years ago and I found out, that the reason were (in my case) double names of pictures in that file. (I use image-filled comments in xlsm.files. I name the pictures in the excel sheet so that they have a connection by name with the comment...)

Everytime you copy a named picture into the same or a different file it gets corrupted because of the identical names.

I got the idea of the name-problem from the internet (I do not know whether in this thread, I did not read it fully again...)

So I rename all shapes which have the type msopicture when the workbook is closed. I helps, the files get no more corrupted, even if you copy comments or delete lines with comments  (which made problems too!)

I do not rename the comments or the shapes of the comment (also not at another place in the code), so I think the names of the comments do not corrupt the file, but the names of the other pictures. As I write this I notice that I have to look at that again.

(slightly simplified code, so it might not work at once)

Private Sub Workbook_BeforeClose(Cancel As Boolean)

   call Bilderbenennen

End Sub

Function Bilderbenennen()

  Dim oShape As Shape

  For Each oShape In ActiveWorkbook.Sheets("Test").Shapes

      ' if the shape is picture

      If oShape.Type = msoPicture Then

        With oShape

                .Select

               If Not .TopLeftCell.Comment Is Nothing Then

                    ' if the cell has a comment, the picture is named as "Bild"+COmmentname

                    .Name = "Bild" & oShape.TopLeftCell.Comment.Shape.Name

                  Else

                    .Name = "ShapeID" & .ID

                End If

            End If

        End With

    End If

  Next

End function

Maybe it can help you?

Best regards

Thomas

If find the following to be a consistent, repeatable problem:

File1 (my master file) with hundreds of pictures within comments.

File2 (my target file) whether a brand new .xlsm or existing .xlsm file.

Create a new picture comment in File1, copy that cell with the comment into File2.  Save File2.  No problem with re-opening afterwards.

However, if I save File1, re-open, then copy that exact same previous cell with the picture comment into File2, Save File2, upon attempt to open File2 Excel says corrupted the same way previous users have indicated.

Therefore saving the file with the created picture comment (me saving File1) somehow causes that created picture comment to be different, so that subsequent copy and paste to other files will corrupt the target. 

The same problem occurs whether I repeat the tests with brand new File1 and File2.  The problem also occurs when copying picture comment cells from one tab to another within the same workbook.  There are other scenarios that result in similar problems.

The problem also occurs in master File1, if I copy an older cell that has a picture comment (subsequent to multiple saves/reopening of File1) without problems; but as soon as a copy an existing cell with picture comment to a cell in another tab, the file is corrupted on the next open attempt.

What appear to work consistently for picture comment operations without corruption is have the target File2 be an XLS (Excel 2003) file.  Unless you must have your file in the new Excel formats, stick with the older file formats.

chasmm,  super easy fix. Saving as a .ODS file worked like a charm.  Thanks!