Excel Error: Removed Records: Sorting from /xl/worksheets/sheet10.xml part

Question

I'm almost sure I will have to create a new excel file, but maybe at least here I get some ideas what was the source of the problem.

My excel file is constantly giving the following error:

Excel found unreadable content in 'filename.xlsm'. Do you want to recover the contents of this workbook? If you trust the source of this workbook, click Yes.

So I do. And the file opens as repaired showing:

Removed Records: Sorting from /xl/worksheets/sheet10.xml part.

The detail is that I don't even have a sheet10

Its a big excel file full of custom-objects. But I have no DB connections or autofilter so could not relate to AutoFilter Criteria Using Array (Error) - Too Large String? or xlsx error: "Removed Records: Named range from /xl/workbook.xml part" when tried to resolve errors.

Anyone has a clue what's going on?

tks in advance

Breadroot

internally, excel stores the worksheets as XML files - numbering the from in ascending order. thus, sheet10.xml will correspond to another worksheet. rename the file to .zip and check out the structure. in xl\workbook.xml you should find the matching worksheet... – Siddra

@PeterAlbert. Im sure its an obvious thing but I' lost:-). I renamed and got filename.zip.xlsm, which is prob not what you meant. – Breadroot

You need to change the settings of windows explorer to also show the file ending - then rename .xlsm to .zip! – Siddra

I had this problem caused by using FreezePanes. Commenting out that line solved it. Excel 2007, EPPlus. – Chromite

Answer 1

I am afraid I cannot explain what is going on, but I had similar experience several times - always with XLSM file. In the latest case I learnt that Excel did not like the same range name used twice, when defined as Worksheet range name (we assumed it is safe to use it that way).

Our troubleshooting path is:

• try to save file as XLSB (binary macro file) and either use XLSB file or save it back to XLSM
• check file history (we always keep daily snapshots) and find when the problem firstly occurred; use previous version and port your changes
• extract the code (via SVN code), create a new workbook and import the code back

Usually Step 1 solves the problem, but Step 3 never failed us.

Serles

Out of curiosity: how exactly do you extract the code in step 3? Is this a manual process? – Siddra

Pretty much so. We use SVN Excel client to extract the code from modules and sheets. Once a new workbook is created, the modules are imported and workbook code is copied. Of course, you will need to manually create worksheets and copy all the formatting and name ranges. Not that nice process. – Serles

@Juliusz. Step one did solve the problem and didnt come back! – Breadroot

Make sure to checkout the answer by @Polybius below! – Mindoro

Answer 2

try clearing the sort before saving down the workbook. When this happened to me I had my sheet references mixed up so the sort wasn't cleared

Sheets(yoursheetname).Sort.SortFields.Clear

Polybius

+1 This is a very simple but useful solution. Thanks – Sulfapyridine

I think this is truly the answer to this question! – Londalondon

This should be accepted as the answer of this question! – Mindoro

I owe you a beverage of your choice! – Ecclesiolatry

I don't understand what this answer is saying – Academy

that's the solution! can you explain why that is so iportant to excel? – Denunciation

Agree this should be the accepted answer - I came here with an .xlsb file that was behaving the same as the user's workbook, so the changing of file type or exploring .xml files was no use to me. This fixed the problem – Blucher

Answer 3

Awesome,

This issue has been driving me crazy!

It seems that saving as the xlsb (binary) file PLUS adding the clearing of sort fields appears to have sorted out my issue.

I added the following code to the BeforeClose sub, it appears to clear the fields really quick, changes to my Dashboard and prompts for saving.

The xlsb file saved over a mb in file size and appears to load a little quicker!

Thanks for the pointers & support!

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim Sht As Worksheet
' Clear all Sort Fields prior to Save & Exit
For Each Sht In Application.Worksheets
    Sht.Sort.SortFields.Clear
Next Sht

aa1_Dashboard1.Select
aa1_Dashboard1.Activate

End Sub

Olid

Your post was my salvation, thank you, thank you! – Outflank

Answer 4

I would like to add my own confirmation regarding clearing sort fields in your spreadsheet, as first suggested by Chris. I specifically had the error occur after I used column sorting in a VBA macro, so I deduced that the problem must be column/field-Sort related. It was. Adding the line: Sheets(yoursheetname).Sort.SortFields.Clear after the sort command allowed the sorted columns to remain sorted (in my case), but cleared whatever was causing the "Sorting from /xl/worksheets/sheet10.xml part." error when the file was next opened.

Many thanks for your support.

Elementary

Answer 5

I did what Chris mentioned above. Clear the sort fields in your spreadsheet.

Sheets(yoursheetname).Sort.SortFields.Clear

Also, save the file as *.xlsb. That should get it to work.

Chide

Answer 6

I don't know if this will help anyone but it's solved my problem. I had an excel sheet, nothing complex, just contacts, a much used file in Excel 2010 on Win7. Today I was using the 'filter' to select specific groups to email targeted content to. In the middle of selecting and copying to paste into Outlook, the document came up with a pop up as if I was installing something, then the 'installer' both of which I cancelled out of. The Document closed and then on trying to reopen, I was getting the error message. If I chose 'yes' to repair I was given just the subset of my filtered list, so about 40 of my 350 records, the rest lost. I googled and tried different things, including opening in Googledocs google sheets and nothing worked. I never 'saved' the repaired version as my old data wasn't in that. Just now, I right clicked on my document in explorer and chose "restore previous versions" and it brought me to the version of yesterday - thankfully, nothing added to it since then. Opened that one and bingo, it opened fine with ALL the data in it. I know the filter had corrupted it, so will be cautious next time I use it. I don't know why it doesn't like the filter but 4 hours on, i can now calm down and relax! Hope this helps someone.

Achondroplasia

Answer 7

was having this issue as well. I have a spreadsheet with multiple tabs (maybe 30 tabs?) and had filters on each sheet, and shared on SharedPoint. The error was not enabling editing of the file on SharePoint. I removed the filters on every sheet and uploaded back to SharePoint and the issue is gone!

Jacobjacoba

Answer 8

When you get a message telling that Excel found unreadable.... just click "Open" and then close all the following messages you get. -In the VBE open the project showing the name of Addin or the .xlsm.. the click anywhere in any module of the workbook as you are going to edit the text of the macro, then click save, select workbook type like Addin select the folder where you want to save it, give it a new name then click "Save", that is all you have to do, the new file you created will ok.

Ebsen