Order by on a recordset does not work when querying a table-CodePudding

I'm using the code below to get the filtered and sorted data from an Excel table. The data resulted is filtered correclty, but it's not sorted. Please let me know if i'm missing anything:

The variables fed to the RangeToRecordset formula are the following.

ExcelFile = ThisWorkbook.FullName
SQL = select * from [Inputs_Source Table$B3:AL2232] WHERE [Worksheet] = 'Global Inputs' AND [Section] = 'GA' ORDER BY 'Worksheet order','SubSection order','Section order'

 Public Function RangeToRecordset(ExcelFile As String, strSQL As String, Optional HasHeader = "Yes") As Recordset
' need to add the following reference:
' Microsoft ActiveX Data object...
' https://online-training.ro/content/vba/range-to-recordset/

    Const adOpenStatic = 3
    Const adLockOptimistic = 3
    Const adCmdText = &H1
    
    Set objConnection = CreateObject("ADODB.Connection")
    Set objRecordset = CreateObject("ADODB.Recordset")
    
    ExcelFile = Replace(ExcelFile, "'", "''") 'DDU [2022.03.04]: ADD THIS TO HANDLE ' IN THE NAME
    
    objConnection.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _
        "Data Source='" & ExcelFile & "';" & _
            "Extended Properties=""Excel 8.0;HDR=" & HasHeader & ";"";"
    
    objRecordset.Open strSQL, _
        objConnection, adOpenStatic, adLockOptimistic, adCmdText
    Set RangeToRecordset = objRecordset
End Function

CodePudding user response：

You are ordering by some fixed strings. Try using field names:

SQL = select * from [Inputs_Source Table$B3:AL2232] WHERE [Worksheet] = 'Global Inputs' AND [Section] = 'GA' ORDER BY [Worksheet order],[SubSection order],[Section order]