使用VBA排序记录的Excel损坏 [英] Excel corruption with VBA sorting records

问题描述

我有一个带有宏的Excel文件，该宏可以过滤记录.运行宏后，我保存并关闭文件.再次打开文件后，它说文件已损坏:

I have an Excel file with a macro that filters records. After running the macro I save and close the file. Once I open the file again it says that the file has been corrupted:

Excel在"[文件名] .xls"中发现了不可读的内容.
您要恢复此工作簿的内容吗?
如果您信任此工作簿的来源，请单击是".

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

单击是"后，文件将打开，并查看将我定向到的XML文件，我将找到以下内容:

Once I click Yes the file opens and looking at the XML file that directs me to I find the following:

<?xml version="1.0" encoding="UTF-8" standalone="true"?>
<recoveryLog xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
    <logFileName>error050360_01.xml</logFileName>
    <summary>Errors were detected in file 'C:\xxx\file.xlsb'</summary>
    <removedRecords summary="Following is a list of removed records:">
        <removedRecord>Removed Records: Sorting from /xl/worksheets/sheet11.bin part</removedRecord>
    </removedRecords>
</recoveryLog>

在Sheet11上，我具有以下排序代码:

On Sheet11 I have the following sorting code:

LastRow = ActiveSheet.Range("A1").Offset(ActiveSheet.Rows.Count - 1, 0).End(xlUp).Row
Range("A3").Select
ActiveWorkbook.Worksheets("AP_PV").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("AP_PV").Sort.SortFields.Add key:=Range("A4:A" & LastRow) _
    , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("AP_PV").Sort
    .SetRange Range("A3:B" & LastRow)
    .Header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With

我尝试在 Range("A3")之前添加条件.选择以查看 LastRow>4 排除排序，但id没有任何作用.

I tried adding a condition before Range("A3").Select to see if LastRow > 4 to exclude the sorting, but id didn't make any difference.

关于导致腐败的原因以及如何避免腐败的任何想法?

Any ideas on what is causing the corruption and how to avoid it?

推荐答案

我只是提供链接就对此发表评论，但是我还没有50的声誉.

I'd comment this, simply providing the link, but I don't have 50 reputation yet.

几年前，Google搜索启用了SE线程，并提供了许多可能的解决方案:

A Google search turned up an SE thread from a few years ago with plenty of possible solutions: Excel Error: Removed Records: Sorting from /xl/worksheets/sheet10.xml part

最可能起作用的就是这个:

Most likely to work is this one:

Sheets(yoursheetname).Sort.SortFields.Clear

将其放在您的排序功能之后(或保存文件之前)，看来应该解决了该问题.

Put that after your sort function (or before saving your file), and it seems the issue should be resolved.

希望如此.

这篇关于使用VBA排序记录的Excel损坏的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！