Excel 2003：不同工作簿中工作表的编程排序

Question

在当前工作簿中，我想在源工作簿中打开一个工作表，关闭可能存在的任何筛选，按一列排序，然后是另一列，然后是另一列。我收到 1004 和其他错误。这需要在 2003 年运行。任何帮助将不胜感激！

Dim WB As Workbook, WasWBOpen As Boolean, srcfile As String, srcpath As String,    
onecol as integer, twocol as integer, thrcol as integer

srcpath = "blahblah"
srcfile = "blah.xls"

On Error Resume Next
Set WB = Workbooks(srcfile)
WasWBOpen = True
On Error GoTo 0
If WB Is Nothing Then
Set WB = Workbooks.Open(srcpath & srcfile, UpdateLinks:=False)
WasWBOpen = False
End If

'code before this opens source wkbook

lstrow = Worksheets("Sheet1").UsedRange.Row - 1 + Worksheets("Sheet1").UsedRange.Rows.Count
lstcol = Worksheets("Sheet1").UsedRange.Column - 1 + Worksheets("Sheet1").UsedRange.Columns.Count

onecol=3
twocol=5
thrcol=8

With WB.Sheets("Sheet1")

.AutoFilterMode = False
.Range("1:1").AutoFilter
'Here's where error occurs--
.Range(Cells(1, 1), Cells(lstrow, lstcol)).Sort _
    Key1:=Columns(onecol), Order1:=xlAscending, _
    Key2:=Columns(twocol), Order2:=xlAscending, _
    Key3:=Columns(thrcol), Order3:=xlAscending, Header:=xlYes

End With

If WasWBOpen = False Then
WB.Close
End If

Answer 1

.Range(Cells(1, 1), Cells(lstrow, lstcol)).Sort _ 

更好地写成：

.Range(.Cells(1, 1), .Cells(lstrow, lstcol)).Sort _ 

Answer 2

我唯一可以尝试的是，您的选择可能不包括第 3、5 或 8 列。错误发生之前，lstrow 和 lstcol 的值是多少？

Answer 3

根据我的经验，您只能对活动工作表进行排序。尝试在 WB.Sheets("Sheet1") 之后添加 .Activate。

有关上次排序的信息存储在工作表中。我有时怀疑这是问题所在，而不是要排序的工作表未处于活动状态。但 .Activate 一直对我有用，所以我从未进一步调查过。

额外信息

我原以为是Sort 产生了错误，但实际上是AutoFilter。

我可以通过将第 1 行留空来生成 1004 错误。

AutoFilter 语句的用途是什么？当 AutoFilterMode = False 时，我希望它返回 Nothing。为什么不删除这条语句呢？

我也担心你正在排序的范围。您减去顶部未使用的行数和左侧未使用的列数来计算 lstrow 和 lstcol，然后在排序中包括那些未使用的行和列。结果是底部的行和右侧的列不会被排序。

如果顶部没有任何未使用的行，左侧没有未使用的列，这并不重要，但您需要决定要排序的范围。

额外信息 2

在我发现了破坏原始代码的第四种方法之后添加了此部分。下面的代码看起来是防弹的。

Option Explicit
Sub TestSort2()

Dim WB As Workbook, WasWBOpen As Boolean, srcfile As String, srcpath As String
Dim onecol As Integer, twocol As Integer, thrcol As Integer

' Undeclared or new variables

Dim InxWB As Long
Dim lstrow As Long
Dim lstcol As Long
Dim srcpathfile As String

' Report the name of the active workbook
Debug.Print "Original active workbook " & ActiveWorkbook.Name

' I created two workbooks named "Failed sort 1.xls" and "Failed sort 2.xls".
' Both are in the same directory. "Failed sort 1.xls" contains this macro.
' "Failed sort 2.xls" contains the data.
srcpath = Application.ActiveWorkbook.Path
srcfile = "Failed sort 2.xls"

srcpathfile = srcpath & "\" & srcfile

WasWBOpen = False
' Check the open workbook for srcfile
For InxWB = 1 To Workbooks.Count
  If Workbooks(InxWB).Name = srcfile Then
    ' Required workbook already open
    Set WB = Workbooks(InxWB)
    WB.Activate                     ' Activate it
    WasWBOpen = True
    Exit For
  End If
Next
If Not WasWBOpen Then
  ' Files was not open
  If Dir(srcpathfile) <> "" Then
    ' File exists
    ' Do you need UpdateLinks:=False?  If there are links
    ' with the sort be affected if they are not updated? 
    Set WB = Workbooks.Open(srcpathfile, UpdateLinks:=False)
  Else
    ' File does not exist
    Call MsgBox(srcpathfile & " does not exist", vbOKOnly)
    Exit Sub
  End If
End If

' WB is now the active workbook whether it was open before or not
Debug.Print "Final active workbook " & ActiveWorkbook.Name      ' Confirm

With Sheets("Sheet1")
  .Activate
  .AutoFilterMode = False

  ' Get the last used row and cell of the worksheet
  lstrow = Cells.SpecialCells(xlCellTypeLastCell).Row
  lstcol = Cells.SpecialCells(xlCellTypeLastCell).Column

  onecol = 3
  twocol = 5
  thrcol = 8

  If onecol > lstcol Or twocol > lstcol Or thrcol > lstcol Then
    Call MsgBox("The sort range does include the sort columns", vbOKOnly)
    If Not WasWBOpen Then
      Close
    End If
    Exit Sub
  End If

  Range(Cells(1, 1), Cells(lstrow, lstcol)).Sort _
        Key1:=Columns(onecol), Order1:=xlAscending, _
        Key2:=Columns(twocol), Order2:=xlAscending, _
        Key3:=Columns(thrcol), Order3:=xlAscending, Header:=xlYes
End With

If Not WasWBOpen Then
  Close
End If

结束子