Excel found unreadable content in "……………".

[springbrook]

I have received and error message:

Excel found unreadable content in "……………". Do you want to recover contents of this workbook?

This workbook has been used for quite some time now.
There are 3 of us who use this workbook nearly every day.
It opens fine on my pc but not on the other 2 (we all run 2007)

Now when they open the workbook it comes up with the following error.
If they open an older version its ok, however if I open it, do nothing to it, save it and then close it, the error appears.

It seems to be since Thursday last week when my 'automatic updates' were done.
Some thing has changed !

Nothing has been altered at all.

I need of desperate help.

Thanks in advance
Springbrook

 

[MLF]

At this point, I have still not found a solution and continue to have the issue.

At least it's nice to know I am not alone.

Frustrating, isn't it?
The "hot-fix" number '314 (http://support.microsoft.com/kb/983314/en-us) did *not* work for my problem.
My problem is closely tied to using the ctrl+alt+shift+f9 to force the rebuild of the calculation dependence chain. I have to do something, as the chain gets corrupted several times a year and gives no symptoms other than wrong calculation results.
Anyway, the work-around to prevent the problem, as another person mentioned, is to replace the equal signs.

Microsoft has recommended that in the past:
http://support.microsoft.com/servicedesks/webcasts/wcd091801/WCD091801.ppt
(Although they also have liked the "new" feature: ctrl+alt+shift+f9)

Because I'm lazy and will have to do this frequently, I just tossed together a couple of short macros.

To convert it...
Sub convertEquationsToText()

For i = 1 To Sheets.Count
Debug.Print Sheets(i).Name

If Sheets(i).Visible = xlSheetVisible Then
Sheets(i).Select
Cells.Replace What:="=", Replacement:="##mlfTrouble##", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
End If

Next i

End Sub


And here's converting it back again...
Sub convertTextToEquations()

For i = 1 To Sheets.Count
Debug.Print Sheets(i).Name

If Sheets(i).Visible = xlSheetVisible Then
Sheets(i).Select

Cells.Replace What:="##mlfTrouble##", Replacement:="=", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False

End If

Next i

End Sub

Note that I'm only changing the visible sheets. That works fine for me, but you can use additional coding to convert hidden and very hidden sheets if you need to.

 

[erock24]

I had this problem and it was due to some sorting vba i was using. I posted an example of my workbook at: http://www.excelforum.com/excel-programming/747218-strange-file-error-using-2007-a.html

Not exactly sure why this worked but I fixed the problem by changing the sub routine code found in the "UtilityMod" module. I changed it from:

Public Function AutoSortTable(ByVal sortRng As Range, ByVal ws As Worksheet, ByVal headVal As Boolean, ByVal sortOne As Long, ByVal sortTwo As Long)
    sortRng.Sort Key1:=ws.Cells(sortRng.Resize(1, 1).Row, sortRng.Resize(1, 1).Column + sortOne), Order1:=xlAscending, _
                 Key2:=ws.Cells(sortRng.Resize(1, 1).Row, sortRng.Resize(1, 1).Column + sortTwo), Order2:=xlAscending, Header:=headVal
End Function

To:

Public Function AutoSortTable(ByVal sortRng As Range, ByVal ws As Worksheet, ByVal headVal As Boolean, ByVal sortOne As Long, ByVal sortTwo As Long)
    Dim sortKey1 As Range, sortKey2 As Range
    Set sortKey1 = ws.Cells(sortRng.Resize(1, 1).Row, sortRng.Resize(1, 1).Column + sortOne)
    Set sortKey2 = ws.Cells(sortRng.Resize(1, 1).Row, sortRng.Resize(1, 1).Column + sortTwo)
    If sortKey1 = sortKey2 Then
        sortRng.Sort Key1:=sortKey1, Order1:=xlAscending, Header:=headVal
    Else
        sortRng.Sort Key1:=sortKey1, Order1:=xlAscending, _
                     Key2:=sortKey2, Order2:=xlAscending, Header:=headVal
    End If
End Function

I got the idea that maybe the code was passing a duplicate key from reading the older posts on page 3. Once I changed the code, ran the macros, and saved the workbook the error went away. Interestingly enough, I tried it without doing the if statement, only setting the sortKey variables and passing those into the sort method, but that didn't work. For what ever reason it need the if statement.

 

[Jim_Dandy]

I had the same problem with unreadable data. I found an easy solution for the sort key issue. Make sure that you clear the sortfields before saving.

ActiveSheet.Sort.SortFields.Clear

It can be maddening Hope it helps.

 

[macarius]

I read through many pages before stumbling on to your response about unsorting... It worked!

I was running a macro to sort a table:

Sub Subsystem_Change()
    With Application
        .ScreenUpdating = False
    End With
 
    ActiveWorkbook.Worksheets("Data").ListObjects("TableNC").Sort.SortFields.Add _
        Key:=Range("BB4"), SortOn:=xlSortOnValues, Order:=xlDescending, _
        DataOption:=xlSortNormal
 
    With ActiveWorkbook.Worksheets("Data").ListObjects("TableNC").Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    With Application
        .ScreenUpdating = True
    End With
End Sub

I added the following at the end and resaved:

    ActiveWorkbook.Worksheets("Data").ListObjects("TableNC").Sort.SortFields.Clear

Success!

Thank you so much!
Mac

 

[cwaraksa]

Thanks to all for your great suggestions to this frustrating problem.

I was having problems when reloading complex reports personalized for many end-users. Recreating files every time the issue surfaced was not going to be feasible, nor was requiring hundreds of end-users to make office or system changes.

Fortunately, I was able to resolve the issue by saving my .xlsx files as .xlsm (even though they had no macros). When they were re-opened they were 'clean' and could be saved as .xlsx again. When I encounter the issue in an .xlsm, I will certainly try clearing the sort fields.

Thank you!

 

[hatman]

One of my co-workers asked my help on a file of his that has suddenly started exhibiting this problem. When I use the Recover option, it does not indicate what (if anything) was repaired. There is one worksheet of values, no worksheet formulas at all. Several Class Modules, standard Code modules and Userforms, as well as a Custom User INterface. I eliminated ALL vba components, and the problem went away. When I import any of these components into a fresh new workbook, the problem transfers to that workbook. If I import any of these components back into the original workbook, the problem returns. If I simply add a new empty VBA component to the original workbook (not importing the originally exported stuff) the problem returns. If I backsave the file to 2003, the problem is still there if the VBA stuff is still in it. I have tried every combination of removing teh VBA components, saving to xlsx, xl 2003, xl 2007, xl 2010, then re-adding ANY VBA compnent to teh file, and the problem returns. Not sure what's going on, but I', still poking at it. Any additional advice would be helpful.

 

[hatman]

This has become very vexing and confusing to me. I'm not sure if this latest finding is related to root cause, or just some sidebar oddity.

After realizing that I could open the file without error if I waited at least a few minutes after closing teh session, I tried the following:

I started a new, blank workbook in a new fresh session of Excel. I inserted a blank code module, and saved it as an xlsm file. Then I closed the session, and used Windows Explorer to open the file, allowing the OS to autoinstantiate a new session of Excel. I then proceeded to close out of Excel without making any changes to the file or re-saving it, and re-open the file through Windows Explorer. At the second or third cycle, I started getting the exact same error message indicating that theere was corruption in the file. Not sure HOW corruption could occur if I have closed teh file without making changes or saving it. I discovered through experimentation that if I waited at least a few minutes before re-openning teh file, the error did not occur. I also found if I closed teh file without ecitting teh Excel session, and then re-opened teh file, no matter how short the cycle time, the error would not occur.

My conclusion is that there is not actually anything wrong with the file, but rather there may be some helper or cache files that get built based on VBA library inclusion that require several seconds to be deleted from the temp drive (at my company, the temp folder is actually located on a share network drive) and when a new session is instantiated before these files are completely deleted, it spurs an error. At the end of of several dozen, perhaps a hundred rapid-fire close/open cycles, I also saw a couple of instances of an error that "File already exists"... seems like Excel was trying to create a file somewhere, but found that it was already there.

 

[SumOtherGuy]

Not sure if folks are still having issues with this, or if the "error" message covers a blanket of other issues, but I also recently had the issue of Excel suddenly telling me there was "unreadable content" in my workbook which was causing errors in other VBA automation I had written.

The issue seemed to lie with the Table I had on one of the worksheets.
To resolve the issue, I simply converted the Table to a normal range and then back to a Table again.

I am thinking the root cause had to due with the data I was pushing (via TransferSpreadsheet) from Access.

I hope this helps!

 

[Dan Rittgers]

This issue seems familiar to my cut and paste protocol from Assess to Excel. Data transfer from Access to Excel was imperfect, in that the conversion of a column of text was pasted as Numbers. The first data cell defines the column in the paste. I would change put an alpha character in the first data cell and then paste into Excel.

Most probably though, if boxes and triangles appear anywhere in the data:
• Decimal, hexadecimal, binary confusion/conversions
• Headers might be present (Hit,f2> key and also look in formula bar after doing)

Not sure if folks are still having issues with this, or if the "error" message covers a blanket of other issues, but I also recently had the issue of Excel suddenly telling me there was "unreadable content" in my workbook which was causing errors in other VBA automation I had written.

The issue seemed to lie with the Table I had on one of the worksheets.
To resolve the issue, I simply converted the Table to a normal range and then back to a Table again.

I am thinking the root cause had to due with the data I was pushing (via TransferSpreadsheet) from Access.

I hope this helps!

 

[Hollando]

Was there ever a Universal root cause/solution found on this? Basically, my result of this error removes four macro enabled buttons from two seperate tabs (error indicating futher that XML drawing 2 and 3 have been removed to ensure the successful repair of the file).

Regards,

Hollando