vba Excel 错误：已删除记录：从 /xl/worksheets/sheet10.xml 部分排序
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20561870/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Excel Error: Removed Records: Sorting from /xl/worksheets/sheet10.xml part
提问by Chris
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.
我几乎可以肯定我将不得不创建一个新的 excel 文件，但也许至少在这里我得到了一些想法问题的根源是什么。
My excel file is constantly giving the following error:
我的excel文件不断出现以下错误：
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.
Excel 在“filename.xlsm”中发现无法读取的内容。是否要恢复此工作簿的内容？如果您信任此工作簿的来源，请单击是。
So I do. And the file opens as repaired showing:
所以我愿意。并且文件以修复方式打开，显示：
Removed Records: Sorting from /xl/worksheets/sheet10.xml part.
删除的记录：从 /xl/worksheets/sheet10.xml 部分排序。
The detail is that I don't even have a sheet10
细节是我什至没有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.
它是一个充满自定义对象的大型 excel 文件。但是我没有数据库连接或自动过滤器，因此无法与使用数组（错误）的自动过滤器标准相关联- 字符串太大？或xlsx 错误：“Removed Records: Named range from /xl/workbook.xml part” 当试图解决错误时。
Anyone has a clue what's going on?
任何人都知道发生了什么？
tks in advance
提前确认
采纳答案by Juliusz
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).
恐怕我无法解释发生了什么，但我有几次类似的经历 - 总是使用 XLSM 文件。在最新的案例中，我了解到 Excel 不喜欢使用两次相同的范围名称，当定义为工作表范围名称时（我们认为以这种方式使用它是安全的）。
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
- 尝试将文件另存为 XLSB（二进制宏文件）并使用 XLSB 文件或将其保存回 XLSM
- 检查文件历史记录（我们总是保留每日快照）并找出问题第一次发生的时间；使用以前的版本并移植您的更改
- 提取代码（通过 SVN 代码），创建一个新的工作簿并将代码导入回来
Usually Step 1 solves the problem, but Step 3 never failed us.
通常第 1 步可以解决问题，但第 3 步从未让我们失望。
回答by Chris
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
回答by BorisNZ
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.
似乎保存为 xlsb（二进制）文件加上添加排序字段的清除似乎已经解决了我的问题。
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.
我将以下代码添加到 BeforeClose 子项中，它似乎可以非常快速地清除字段，更改我的仪表板并提示保存。
The xlsb file saved over a mb in file size and appears to load a little quicker!
xlsb 文件的文件大小超过了 mb，并且加载速度似乎更快了！
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
回答by Tracy
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.
我不知道这是否会帮助任何人，但它解决了我的问题。我有一个 Excel 表，没有什么复杂的，只是联系人，一个在 Win7 上的 Excel 2010 中经常使用的文件。今天我正在使用“过滤器”来选择特定组以通过电子邮件发送目标内容。在选择和复制以粘贴到 Outlook 的过程中，文档弹出了一个好像我在安装某些东西的弹出窗口，然后是我取消的“安装程序”。文档关闭，然后在尝试重新打开时，我收到错误消息。如果我选择“是”来修复，我只会得到过滤列表的子集，所以我的 350 条记录中约有 40 条丢失了。我用谷歌搜索并尝试了不同的东西，包括在 Googledocs 谷歌表格中打开，但没有任何效果。我从来没有“保存”修复过的版本，因为我的旧数据是“ 在那个。刚才，我在资源管理器中右键单击了我的文档并选择了“恢复以前的版本”，它使我回到了昨天的版本 - 谢天谢地，此后没有添加任何内容。打开那个和宾果游戏，它打开得很好，里面有所有数据。我知道过滤器损坏了它，所以下次使用时要小心。我不知道为什么它不喜欢过滤器，但 4 小时后，我现在可以冷静下来放松了！希望这可以帮助某人。不喜欢过滤器，但 4 小时后，我现在可以冷静下来放松了！希望这可以帮助某人。不喜欢过滤器，但 4 小时后，我现在可以冷静下来放松了！希望这可以帮助某人。
回答by Jonathan M.
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!
也有这个问题。我有一个包含多个选项卡（可能有 30 个选项卡？）的电子表格，每张表上都有过滤器，并在 SharedPoint 上共享。错误是无法在 SharePoint 上编辑文件。我删除了每张纸上的过滤器并上传回 SharePoint，问题就解决了！
回答by MKDev
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.
此外，将文件另存为 *.xlsb。这应该让它工作。
回答by Ghassan Mhanna
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.
当您收到一条消息，告诉您 Excel 无法读取……只需单击“打开”，然后关闭您收到的所有以下消息。- 在 VBE 中打开显示 Addin 名称或 .xlsm 的项目。当您要编辑宏的文本时，在工作簿的任何模块中单击任意位置，然后单击保存，选择工作簿类型，如 Addin 选择文件夹，给它一个新名称，然后点击“保存”，这就是你所要做的，你创建的新文件就可以了。
回答by Richard
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.
我想添加我自己的关于清除电子表格中的排序字段的确认，正如 Chris 首先建议的那样。我在 VBA 宏中使用列排序后特别发生了错误，所以我推断问题必须与列/字段排序相关。它是。添加行： Sheets(yoursheetname).Sort.SortFields.Clear 在 sort 命令允许排序后的列保持排序（在我的情况下），但清除了导致“从 /xl/worksheets/sheet10.xml 部分排序”的任何内容。 ” 下次打开文件时出错。
Many thanks for your support.
非常感谢您的支持。
