vba - 无法使用 Range.Sort 对 XLS 数据进行排序

Question

我有一个 xl 文件，其中包含从 A 到 H 的大约 2000 行和列。我试图根据 D 列对文件进行排序，以便所有其他列也相应地排序(扩展选择区域)。

我对宏很陌生，一直在做这个小任务来节省我的报告时间。

这是我尝试过的:

提示用户选择文件

将列从 A 设置为 H

排序范围为 D2

保存文件

正如我所说，我是新手，我使用了 MSDN 库中示例示例中的大部分代码。除了 Sort() 之外，其他一切都对我有用。

这是代码

Sub Select_File_Windows()
    Dim SaveDriveDir As String
    Dim MyPath As String
    Dim Fname As Variant
    Dim N As Long
    Dim FnameInLoop As String
    Dim mybook As Workbook
    Dim SHEETNAME As String

    'Default Sheet Name
    SHEETNAME = "Sheet1"

    ' Save the current directory.
    SaveDriveDir = CurDir

    ' Set the path to the folder that you want to open.
    MyPath = Application.DefaultFilePath

    ' Open GetOpenFilename with the file filters.
    Fname = Application.GetOpenFilename( _
            FileFilter:="XLS Files (*.xls),*.xls,XLSX Files (*.xlsx),*.xlsx", _
            Title:="Select a file", _
            MultiSelect:=True)

    ' Perform some action with the files you selected.
    If IsArray(Fname) Then
        With Application
            .ScreenUpdating = False
            .EnableEvents = True
        End With

        For N = LBound(Fname) To UBound(Fname)

            ' Get only the file name and test to see if it is open.
            FnameInLoop = Right(Fname(N), Len(Fname(N)) - InStrRev(Fname(N), Application.PathSeparator, , 1))
            If bIsBookOpen(FnameInLoop) = False Then

                Set mybook = Nothing
                On Error Resume Next
                Set mybook = Workbooks.Open(Fname(N))
                On Error GoTo 0

                DoEvents

                If Not mybook Is Nothing Then
                    Debug.Print "You opened this file : " & Fname(N) & vbNewLine

                    With mybook.Sheets(SHEETNAME)

                        'Columns("A:H").Sort Key1:=Range("D2:D2000"), Order1:=xlAscending, Header:=xlYes
                        'Range("A1:H2000").Sort Key1:=Range("D1"), Order1:=xlAscending
                        Columns("A:H").Sort Key1:=Range("D1"), Order1:=xlAscending, Header:=xlYes

                    End With

                    Debug.Print "Sorter Called"

                    mybook.Close SaveChanges:=True
                End If
            Else
                Debug.Print "We skipped this file : " & Fname(N) & " because it is already open. Please close the data file and try again"
        End If
        Next N
        With Application
            .ScreenUpdating = True
            .EnableEvents = True
        End With
    End If

End Sub


Function bIsBookOpen(ByRef szBookName As String) As Boolean
    On Error Resume Next
    bIsBookOpen = Not (Application.Workbooks(szBookName) Is Nothing)
End Function

没有什么对我有用。该文件保持原样，不会对其进行更新。我不明白，我在这里犯的新手错误是什么？

请帮忙。

引用:

https://msdn.microsoft.com/en-us/library/office/ff840646(v=office.15).aspx

http://analysistabs.com/vba/sort-data-ascending-order-excel-example-macro-code/

Run time error 1004 when trying to sort data on three different values

Answer 1

它可能就像添加几个点一样简单(见下面的倒数第二行)

With mybook.Sheets(SHEETNAME)
    'Columns("A:H").Sort Key1:=Range("D2:D2000"), Order1:=xlAscending, Header:=xlYes
       'Range("A1:H2000").Sort Key1:=Range("D1"), Order1:=xlAscending
       .Columns("A:H").Sort Key1:=.Range("D1"), Order1:=xlAscending, Header:=xlYes
End With