Sort Problem: Unreadable Content

[Virtual Blue]

I am getting this message come up often when I open my workbook:"Excel found unreadable content in 'mybook.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 get another dialog box with the following information:"Excel was able to open the file by repairing or removing the unreadable content.Removed Records: Sorting from /xl/worksheets/sheet3.xml part"It tends to vary between 3 different sheets although once the message came up relating to 2 sheets.Can anyone tell me why this would be coming up and what I should be looking for to prevent it from happening?ThanksVB

 

[Virtual Blue]

I'm thinking that this may have something to do with custom lists. Does anyone know if the sheet was sorted by a custom list and that list is subsequently deleted - will it cause a problem? I've had a couple of further instances where Excel 07 has crashed after I used procedures to create 4 custom lists, sort the sheet, then delete the custom lists. I presume that somewhere in the xml the reference to the lists are required to maintain the structure of the sheet?

 

[Gibbers]

I had the very same problem.

It appears that this problem is related to the length of the custom list string which is used to sort your worksheet.

The new format file format for Excel retains in an XML node, the sort criteria of the file when it is saved. I had a very long custom list used for sorting. It was 350+ characters.

I modified the string by editing the XML to shorten it to around ~225 characters, and when I reopened the file, no error message.

My testing also showed that if I sorted the file by a single column and then saved it, I could reopen the file with no errors.

I suspect the limit may be 255 characters.

Curiously, once the file is open I didn't notice any issues sorting the file using my "long" custom list. Excel appears to report an error only when opening the file.

Hope this helps.

 

[Virtual Blue]

Thanks Gibbers. I have one custom list that is 392 characters in length. I couldn't reduce the length and in fact it is likely to get even longer. As a work around I was in the middle of writing another procedure that ran after every time my sort code ran. That procedure would put the current row numbers in a designated index column then I would sort on those row numbers. In the middle of doing that though I wondered if using .SortFields.Clear at the end of my sort code would help and it seems it has. I no longer get the error appearing when I open the workbook. Cheers - VB

 

[cxchen47]

I had the same problem and found that it is cause by my custom list added by a macro, which added the same sorting field twice, but vba didn't raise the error and sorting is succseful. So it is a coding issue, a bug on my part.

 

[Michael Simpson]

I was having the same problem as well (and I was also running a customorder as shown here as well as sorting 2 other columns at the same time)

' Note how we DELIBERATELY sort the rows in column G so that "Settlement" automatically
' comes first. This makes the logic much easier in the rest of the code
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range(temp_g) _
    , SortOn:=xlSortOnValues, Order:=xlAscending, CustomOrder:= _
        "Settlement," & _
        "Loro," & _
        "O/N," & _
        "Revrepos," & _
        "S/T Credits," & _
        "S/T Programs," & _
        "L/T Programs," & _
        "S/T Gtee & L/C," & _
        "L/T Gtee & L/C," & _
        "S/T Derivatives," & _
        "L/T Derivatives" _
        , DataOption:=xlSortNormal

I copied these values to Word and ascertained that the number of characters was only 117, so that didn't seem to be the problem.

I also noticed that selecting Sort, I'd (somehow or other) suddenly got 9 sorting levels, repeated in groups of 3. This might have come about from copying VBA code from elsewhere to create custom lists.

I added the .SortFields.Clear as Virtual Blue suggested, and the problem seems to have gone away for the time being (permanently, hopefully)

 

[RogerDodger]

The "unreadable content" with reference to "sheet2.bin" has plagued one of my workbooks. I finally (after a year!) figured out how to fix the problem with much help from this thread. The solution is to add one line of code after the End With statement used to do a VBA sort. the line of code is

I think it is a

 

[RogerDodger]

Hi Forum, The "unreadable content" with reference to "sheet2.bin" has plagued two of my workbooks for over a year now. When it ‘infected’ the 2nd workbook, it was after I added some code to do a sort, so that was my lucky break that I knew it had something to do with sorting. The error did not stop me from using the workbook but everytime I opened it I was greeted with an error message that unreadable content was detected and removed and some vauge referene to sheet2.bin. I finally figured out how to fix the problem with help from this thread (forums are great). The solution is to add one line of code after the End With statement used to do a VBA sort. The line of code is this:
##

ActiveWorkbook.Worksheets("name of workshet or use code name").Sort.SortFields.Clear

##
Here is a more realistic example:
##

ActiveWorkbook.Worksheets("name").ListObjects("Table_SQL...").Sort.SortFields.Add _
        Key:=Range("Table_SQL….."), _
        SortOn:=xlSortOnValues, _
        Order:=xlDescending, _
        DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("name").ListObjects("Table_SQL…").Sort
    .Header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With
ActiveWorkbook.Worksheets("name").Sort.SortFields.Clear [COLOR="#008000"]'<--added this line to resolve “unreadable content” problem.[/COLOR]

##
I can report that this has completely solved the problem for both my workbooks so confidence is high that this is the solution. It seems to me that the need to 'clear' sortfields is an Excel bug but then this problem can be resolved by adding a line of code (sortfields.clear), so is this a bug or a programming error? I speculate it is the latter but maybe someone can explain what is really happening. Is a sortfield some kind of residual data that a VBA sponsored sort creates, and writes to a non-viewable scratch pad called sheet2.bin that is off limits to developers.

 

[fshhady]

thank you Mr.RogerDodger for your post it saved me really. But still need to know why such problems occur. I appreciate it a lot if you know why and share it with us.

 

[RoryA]

It's because of the unqualified reference to Range("Table_SQL…..") - it needs a worksheet qualifier.