why the sorting Doesn't Happen?

[Prabhakaran_Karuppaih]

Option Compare Database

Private Sub MailFolder_Browse_Click()
Dim MailFolderDiag As FileDialog
Set MailFolderDiag = Application.FileDialog(msoFileDialogFolderPicker)
With MailFolderDiag
    .AllowMultiSelect = False
    .Show
End With
Me.MailFolder.Value = MailFolderDiag.SelectedItems(1)
Dim CurrentFile As Variant
CurrentFile = Dir(Me.MailFolder.Value & "\", vbNormal)
Do While CurrentFile <> ""
    'MsgBox CurrentFile
   Dim myXLapp As New Excel.Application
   With myXLapp
        .Visible = True
        With .Workbooks.Open(FileName:=Me.MailFolder.Value & "\" & CurrentFile, ReadOnly:=True)
            'MsgBox .Name
            Dim VisibleIndex As Integer
            VisibleIndex = 0
            Dim SheetNamesWithIndex() As Variant
            For Each Sheet In .Sheets
                With Sheet
                    If .Visible = True Then
                        If VisibleIndex = 0 Then
                            ReDim Preserve SheetNamesWithIndex(0, 1)
                        Else
                            ReDim Preserve SheetNamesWithIndex(0, UBound(SheetNamesWithIndex, 2) + 2)
                        End If
                        SheetNamesWithIndex(0, UBound(SheetNamesWithIndex, 2) - 1) = Sheet.Index
                        SheetNamesWithIndex(0, UBound(SheetNamesWithIndex, 2)) = CDate(Split(.Name, "-")(UBound(Split(.Name, "-"))) & "/01")
                        VisibleIndex = VisibleIndex + 1
                    End If
                End With
            Next Sheet
            Dim SortedSheetNames As Variant
            SortedSheetNames = myXLapp.WorksheetFunction.Sort(SheetNamesWithIndex, 2, -1)
            MsgBox SortedSheetNames(1)
            .Close
        End With
        .Quit
    End With
    CurrentFile = Dir()
Loop
End Sub

Exactly Here SortedSheetNames = myXLapp.WorksheetFunction.Sort(SheetNamesWithIndex, 2, -1)

 

[Special-K99]

Cross-posted here (though no mention of it)

why the sorting Doesn't Happen?

Option Compare Database Private Sub MailFolder_Browse_Click() Dim MailFolderDiag As FileDialog Set MailFolderDiag = Application.FileDialog(msoFileDialogFolderPicker) With MailFolderDiag .AllowMultiSelect = False .Show End With Me.MailFolder.Value = MailFolderDiag.SelectedItems(1) Dim...

www.access-programmers.co.uk