Get Sheet Names - Remove Sort from this code

This code works well to get sheet names form a closed workbook. But can someone tell me how to remove the sorting Alphabetical form this code? Its a little too complex for me I dont know how to remove the sort.

Option Explicit

Public Sub DemoGetSheetNames()

    Dim lNumEntries As Long
    Dim szFullName As String
    Dim szFileSpec As String
    Dim aszSheetList() As String
    
    Columns("A:A").Select
    Selection.ClearContents

    szFileSpec = "Excel Files (*.xl*),*.xl*"
    
    szFullName = CStr(Application.GetOpenFilename(szFileSpec, , "Select an Excel File"))
    
    ''' Continue if the user did not cancel the dialog.
    If szFullName <> CStr(False) Then
        GetSheetNames szFullName, aszSheetList()
        lNumEntries = UBound(aszSheetList) - LBound(aszSheetList) + 1
        Sheet1.Range("A1").Resize(lNumEntries).Value = Application.WorksheetFunction.Transpose(aszSheetList())
        Sheet1.Range("A1").EntireColumn.AutoFit
    End If

End Sub


''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
''' Comments:   Returns a string array containing the list of worksheets in
'''             the specified workbook.
'''             NOTE: Requires references to the following object library:
'''             * Microsoft ActiveX Data Objects 2.5 Library (or higher version)
'''
''' Arguments:  szFullName      [in] The full path and filename of the workbook
'''                             whose worksheet list you want to query.
'''             aszSheetList()  [out] Will be loaded with a list of worksheets
'''                             in the workbook specified by szFullName.
'''
''' Date        Developer       Action
''' --------------------------------------------------------------------------
''' 05/13/05    Rob Bovey       Created
'''
Private Sub GetSheetNames(ByRef szFullName As String, ByRef aszSheetList() As String)

    Dim bIsWorksheet As Boolean
    Dim objConnection As ADODB.Connection
    Dim rsData As ADODB.Recordset
    Dim lIndex As Long
    Dim szConnect As String
    Dim szSheetName As String

    Erase aszSheetList()
    If Application.Version < 12 Then
        szConnect = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & szFullName & ";Extended Properties=Excel 8.0;"
    Else
        szConnect = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & szFullName & ";Extended Properties=Excel 8.0;"
    End If

    Set objConnection = New ADODB.Connection
    objConnection.Open szConnect
    Set rsData = objConnection.OpenSchema(adSchemaTables)

    Do While Not rsData.EOF
        bIsWorksheet = False
        szSheetName = rsData.Fields("TABLE_NAME").Value
        If Right$(szSheetName, 1) = "$" Then
            ''' This is a simple sheet name. Remove the trailing "$" and continue.
            szSheetName = Left$(szSheetName, Len(szSheetName) - 1)
            bIsWorksheet = True
        ElseIf Right$(szSheetName, 2) = "$'" Then
            ''' This is a sheet name with spaces and/or special characters.
            ''' Remove the right "&'" characters.
            szSheetName = Left$(szSheetName, Len(szSheetName) - 2)
            ''' Remove the left single quote character.
            szSheetName = Right$(szSheetName, Len(szSheetName) - 1)
            bIsWorksheet = True
        End If
        If bIsWorksheet Then
            ''' Embedded single quotes in the sheet name will be doubled up.
            ''' Replace any doubled single quotes with one single quote.
            szSheetName = Replace$(szSheetName, "''", "'")
            ''' Load the processed sheet name into the array.
            ReDim Preserve aszSheetList(0 To lIndex)
            aszSheetList(lIndex) = szSheetName
            lIndex = lIndex + 1
        End If
        rsData.MoveNext
    Loop

    rsData.Close
    Set rsData = Nothing
    objConnection.Close
    Set objConnection = Nothing

End Sub

I have been trying to get this to work and having issues. it clears the existing A Column but doesn't add back the sheet names to the file I open. Before it didn't actually open the file i am pulling the sheet names from (which is how I wanted it), and now its trying to open it (because its activating a macro within the file I am selecting). I have to change the column to B not sure of that screwed it up?

This is the original code with B being the destination

Option Explicit

Public Sub DemoGetSheetNames()

    Dim lNumEntries As Long
    Dim szFullName As String
    Dim szFileSpec As String
    Dim aszSheetList() As String
    
    Columns("B:B").Select
    Selection.ClearContents

    szFileSpec = "Excel Files (*.xl*),*.xl*"
    
    szFullName = CStr(Application.GetOpenFilename(szFileSpec, , "Select an Excel File"))
    
    ''' Continue if the user did not cancel the dialog.
    If szFullName <> CStr(False) Then
        GetSheetNames szFullName, aszSheetList()
        lNumEntries = UBound(aszSheetList) - LBound(aszSheetList) + 1
        Sheet1.Range("B3").Resize(lNumEntries).Value = Application.WorksheetFunction.Transpose(aszSheetList())
        Sheet1.Range("B3").EntireColumn.AutoFit
    End If

End Sub


''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
''' Comments:   Returns a string array containing the list of worksheets in
'''             the specified workbook.
'''             NOTE: Requires references to the following object library:
'''             * Microsoft ActiveX Data Objects 2.5 Library (or higher version)
'''
''' Arguments:  szFullName      [in] The full path and filename of the workbook
'''                             whose worksheet list you want to query.
'''             aszSheetList()  [out] Will be loaded with a list of worksheets
'''                             in the workbook specified by szFullName.
'''
''' Date        Developer       Action
''' --------------------------------------------------------------------------
''' 05/13/05    Rob Bovey       Created
'''
Private Sub GetSheetNames(ByRef szFullName As String, ByRef aszSheetList() As String)

    Dim bIsWorksheet As Boolean
    Dim objConnection As ADODB.Connection
    Dim rsData As ADODB.Recordset
    Dim lIndex As Long
    Dim szConnect As String
    Dim szSheetName As String

    Erase aszSheetList()
    If Application.Version < 12 Then
        szConnect = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & szFullName & ";Extended Properties=Excel 8.0;"
    Else
        szConnect = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & szFullName & ";Extended Properties=Excel 8.0;"
    End If

    Set objConnection = New ADODB.Connection
    objConnection.Open szConnect
    Set rsData = objConnection.OpenSchema(adSchemaTables)

    Do While Not rsData.EOF
        bIsWorksheet = False
        szSheetName = rsData.Fields("TABLE_NAME").Value
        If Right$(szSheetName, 1) = "$" Then
            ''' This is a simple sheet name. Remove the trailing "$" and continue.
            szSheetName = Left$(szSheetName, Len(szSheetName) - 1)
            bIsWorksheet = True
        ElseIf Right$(szSheetName, 2) = "$'" Then
            ''' This is a sheet name with spaces and/or special characters.
            ''' Remove the right "&'" characters.
            szSheetName = Left$(szSheetName, Len(szSheetName) - 2)
            ''' Remove the left single quote character.
            szSheetName = Right$(szSheetName, Len(szSheetName) - 1)
            bIsWorksheet = True
        End If
        If bIsWorksheet Then
            ''' Embedded single quotes in the sheet name will be doubled up.
            ''' Replace any doubled single quotes with one single quote.
            szSheetName = Replace$(szSheetName, "''", "'")
            ''' Load the processed sheet name into the array.
            ReDim Preserve aszSheetList(0 To lIndex)
            aszSheetList(lIndex) = szSheetName
            lIndex = lIndex + 1
        End If
        rsData.MoveNext
    Loop

    rsData.Close
    Set rsData = Nothing
    objConnection.Close
    Set objConnection = Nothing

End Sub

It doenst work with the XLSB file I'm trying to do it with. But my original issue is the sorting, I didnt want it to sort A-Z. Did your code fix this?

That was absolutely genius!! Thank you so much!

How can I get the sheet name list to start in row 3 instead of row 2?

I took an online course a few years ago. If you know any good ones let me know (seriously no sarcasm). I really appreciate your help!

Thank you!