why the sorting Doesn't Happen?

Joined
May 3, 2021
Messages
11
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
  2. Web
VBA Code:
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)
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
Cross-posted here (though no mention of it)
 
Upvote 0

Forum statistics

Threads
1,215,205
Messages
6,123,632
Members
449,109
Latest member
Sebas8956

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top