Sort a Table in Excel Instance

Full disclosure I posted this code on another forum, but it was for a different questions that was resolved.

I have a piece of MS Access code that opens an excel instance. Once open Access appends new records to a table. Think most recent monthly detail. This works great. My problem is that after the data is appended, I want to sort the table in descending order. Below is my code.

I should mention the one line of code works as expected if I just paste it into the regular excel file, (not the instance opened by MS Access)

'Change True to False if you do not want the workbook to be
'Visible when the code is running
    excelApp.Visible = False

'Open the target workbook
    Set targetWorkbook = excelApp.Workbooks.Open(sFilePath & sDepartment & sSubFolder & sDepartment & "_YTD_DETAIL" & ".xlsm")
     Debug.Print targetWorkbook.Name
'    Debug.Print "Excel File " & sDepartment & " Opened"
'    Debug.Print sFilePath & sDepartment & sSubFolder & sDepartment & "_YTD_DETAIL" & ".xlsm"
    
    'Dim tbl As ListObject
    Dim sTable As String
    Dim LastRow As Long
    
    For Each oSheet In targetWorkbook.Worksheets
            If oSheet.Name = "EXHIBIT_2_DETAIL_2020" Then
                Debug.Print oSheet.Name
                With oSheet.ListObjects("DETAIL_2020")
                    LastRow = oSheet.Range("DETAIL_2020").Rows.Count + FirstRow
                    Debug.Print LastRow
                    .Range("A" & LastRow).CopyFromRecordset rsQuery_expense
                



                ' THE FOLLOWING LINE OF CODE!!
                ' Sort table descending newest date first )WIP
                    .Range("A1", Range("D" & Rows.Count).End(xlUp)).Sort [E2], xlDescending, Header:=xlYes





                End With
                'Debug.Print "Completed the export of Expense Detail For " & sDepartment
            ElseIf oSheet.Name = "HEAD_TEMP_COUNT" Then
                oSheet.Range("D3").CopyFromRecordset rsQuery_head
                oSheet.Range("D9").CopyFromRecordset rsQuery_temp_head
                Debug.Print "Completed the export of Head Count and Temp Head Count For " & sDepartment
            ElseIf oSheet.Name = "PIVOTS" Then
                oSheet.Range("A1").Value = "EXPENSES REPORT UPDATED: " & Now
                Debug.Print "Report Updated to reflect " & Now & " Timestamp"
            ElseIf oSheet.Name = "ACTUALS_VS_PLAN" Then
                oSheet.Range("A1").Value = Month(Date) - 1
            End If 'There will be other sheets in workbook, but the 2 above are the only ones i need to interact with.
    Next oSheet

When I run it as pasted above I get a compiler error saying "Sub or Function not defined" With the word "Range highlighted.
I'm sure I am making this harder than it needs to be. Any help or guideline at this point would be greatly appreciated.

@Greg M;

Using values of the constants would be better.... i.e.;

Const xlDescending = 2
Const xlYes = 1
'
'
'
.Range("A1", .Range("D" & Rows.Count).End(xlUp)).Sort [E2], xlDescending, xlYes

Hi Haluk,

Thanks for your feedback.

That's a good approach, and is a LOT better than inserting hard-coded values directly in the command itself, i.e.:

    .Range("A1", .Range("D" & Rows.Count).End(xlUp)).Sort [E2], 2, 1

A possible slight advantage of the approach I suggested might be that it "future-proofs" the code against any Microsoft decision to change (for whatever reason!) the values of the constants.

Regards,

Greg M

Thank you very much for taking the time to answer my question. I added the code you suggested, but am still unable to get the table to sort.
Here is my updated code:

    Dim sTable As String
    Dim LastRow As Long

' Added this line
    Const xlDescending = 2
    Const xlYes = 1
    
   For Each oSheet In targetWorkbook.Worksheets
            If oSheet.Name = "EXHIBIT_2_DETAIL_2020" Then
                'Debug.Print oSheet.Name
                With oSheet.ListObjects("DETAIL_2020")
                    LastRow = oSheet.Range("DETAIL_2020").Rows.Count + FirstRow
                    'Debug.Print LastRow
                    oSheet.Range("A" & LastRow).CopyFromRecordset rsQuery_expense


' Added this line
                    oSheet.Range("A1", .Range("W" & Rows.Count).End(xlUp)).Sort [E2], 2, 1

                    
                    ' Sort table decending newest date first )WIP
                    'Range("A1:W" & LastRow).Sort Key5:=Range("E:E"), Order1:=xlDescending, Header:=xlNo
                End With
                'Debug.Print "Completed the export of Expense Detail For " & sDepartment
            ElseIf oSheet.Name = "HEAD_TEMP_COUNT" Then
                oSheet.Range("D3").CopyFromRecordset rsQuery_head
                oSheet.Range("D9").CopyFromRecordset rsQuery_temp_head
                Debug.Print "Completed the export of Head Count and Temp Head Count For " & sDepartment
            ElseIf oSheet.Name = "PIVOTS" Then
                oSheet.Range("A1").Value = "EXPENSES REPORT UPDATED: " & Now
                Debug.Print "Report Updated to reflect " & Now & " Timestamp"
            ElseIf oSheet.Name = "ACTUALS_VS_PLAN" Then
                oSheet.Range("A1").Value = Month(Date) - 1
            End If 'There will be other sheets in workbook, but the 2 above are the only ones i need to interact with.
    Next oSheet

Hi again,

Once again this is an untested suggestion - anything else is almost impossible for me as I don't have access to your database/workbooks etc.

I'm assuming that the fault is occurring on the line marked as "Added this line", so try:

    oSheet.Range("A1", oSheet.Range("W" & Rows.Count).End(xlUp)).Sort [E2], 2, 1

Hope this helps - as before, please let me know how you get on.

Regards,

Greg M

Thank you everyone. I wasn't able to get it working, here is my full code. Like I stated above, the line to sort the table works in a regular excel file, but not the instance opened through MS Access. So I don't know if it has any value attaching a workbook, and it would be a ton of work to dial back the MS Access database and upload that and the corresponding excel file.

If anyone see something wrong in my code, it would be appreciated, otherwise I will close the question because Ia m not able to provide enough detail.

Thanks again,

Private Sub Command47_Click()

'BOF checks if current record position is before the first record
'EOF checks if current record position is after the last record
'If BOF and EOF both return TRUE, then the Table has no record
'rs.MoveFirst ‘makes the first record the current record, in case current record is not the first record

'Error handling
    'On Error GoTo Error_Handler
    

Dim sCost As String
Dim rsQuery_expense As DAO.Recordset
Dim rsQuery_head As DAO.Recordset
Dim rsQuery_temp_head As DAO.Recordset
Dim rs As DAO.Recordset
Dim dbs As DAO.Database
Dim excelApp As Object
Dim sFilePath As String
Dim sDepartment As String
Dim oSheet As Object
Dim oBook As Object


'This RS is departments who are a stakeholder in an Exhibit 2 line
Set dbs = CurrentDb
Set rs = dbs.OpenRecordset("SELECT * FROM qryDepartmentActive")


'Set Variables
sFilePath = "Y:\Budget process information\BUDGET DEPARTMENTS\"
sSubFolder = "\MONTHLY EXPENSE REPORTS\"


'Check to see if the recordset actually contains rows
'Do until there are no more records in the RS
If Not (rs.EOF And rs.BOF) Then
    rs.MoveFirst
    Do Until rs.EOF = True


'sCost_Center = 100
'sDepartment = DLookup("DEPARTMENT", "qryDepartment", "COST_CENTER =" & rs.Fields("COST_CENTER"))
'sCost_Center = rs.Fields("COST_CENTER")
sCost_Center = 100
sDepartment = DLookup("DEPARTMENT", "qryDepartment", "COST_CENTER = " & sCost_Center)


'Specify the query to be exported
    Set rsQuery_expense = dbs.OpenRecordset("SELECT Statement")
    'Set rsQuery_head = dbs.OpenRecordset("SELECT Statement")
    'Set rsQuery_temp_head = dbs.OpenRecordset(""SELECT Statement")
    
Debug.Print "The Ledger table contains " & rsQuery_expense.RecordCount & " records."


'Open an instance of Excel
    On Error Resume Next
    Set excelApp = GetObject(, "Excel.Applicationn")

    If Err.NUMBER <> 0 Then
    'Err.Clear
    'On Error GoTo Error_Handler
    Set excelApp = CreateObject("Excel.Application")
    End If
    Debug.Print "Excel Instance Created"


'Change True to False if you do not want the workbook to be
'Visible when the code is running
    excelApp.Visible = False

'Open the target workbook
    Set targetWorkbook = excelApp.Workbooks.Open(sFilePath & sDepartment & sSubFolder & sDepartment & "_YTD_DETAIL" & ".xlsm")
     Debug.Print targetWorkbook.Name
'    Debug.Print "Excel File " & sDepartment & " Opened"
'    Debug.Print sFilePath & sDepartment & sSubFolder & sDepartment & "_YTD_DETAIL" & ".xlsm"
    
    'Dim tbl As ListObject
    
    Dim sTable As String
    Dim LastRow As Long
    Const xlDescending = 2
    Const xlYes = 1
    
   For Each oSheet In targetWorkbook.Worksheets
            If oSheet.Name = "EXHIBIT_2_DETAIL_2020" Then
                'Debug.Print oSheet.Name
                With oSheet.ListObjects("DETAIL_2020")
                    LastRow = oSheet.Range("DETAIL_2020").Rows.Count + FirstRow
                    'Debug.Print LastRow
                    oSheet.Range("A" & LastRow).CopyFromRecordset rsQuery_expense


  'THIS LINE                  
                     'oSheet.Range("A1", oSheet.Range("W" & oSheet.Rows.Count).End(xlUp)).Sort oSheet.Range("E2"), 2, 1

                     
                    ' Sort table decending newest date first )WIP
                    'Range("A1:W" & LastRow).Sort Key5:=Range("E:E"), Order1:=xlDescending, Header:=xlNo
                
                
                End With
                'Debug.Print "Completed the export of Expense Detail For " & sDepartment
            ElseIf oSheet.Name = "HEAD_TEMP_COUNT" Then
                oSheet.Range("D3").CopyFromRecordset rsQuery_head
                oSheet.Range("D9").CopyFromRecordset rsQuery_temp_head
                Debug.Print "Completed the export of Head Count and Temp Head Count For " & sDepartment
            ElseIf oSheet.Name = "PIVOTS" Then
                oSheet.Range("A1").Value = "EXPENSES REPORT UPDATED: " & Now
                Debug.Print "Report Updated to reflect " & Now & " Timestamp"
            ElseIf oSheet.Name = "ACTUALS_VS_PLAN" Then
                oSheet.Range("A1").Value = Month(Date) - 1
            End If 'There will be other sheets in workbook, but the 2 above are the only ones i need to interact with.
    Next oSheet
    

'Close the EXCEL file while saving the file, and clean up the EXCEL objects
    Set excelApp = Nothing
    
    targetWorkbook.Close True
    Debug.Print sDepartment & " Excel Workbook has been saved and Closed"
    
    Set targetWorkbook = Nothing

    'Debug.Print that we are moving to the next record with 2 line breaks in between
    Debug.Print "Moving to the next Recordset" & vbNewLine & StringTwo
    
    Loop

'Move to the next recordset
    rs.MoveNext
    Debug.Print "Moving to the next Recordset" & vbNewLine & StringTwo

Else
    MsgBox "There are no records in the recordset."
End If

MsgBox "Finished looping through records."

'Close the recordset & clean up
    rs.Close
    Set rs = Nothing



Error_Handler_Exit:

'Close the EXCEL file while saving the file, and clean up the EXCEL objects
    Set excelApp = Nothing
    targetWorkbook.Close True
    Set targetWorkbook = Nothing
    rs.MoveNext

    Exit Sub

Error_Handler:
    Select Case Err.NUMBER
        Case 2302
            MsgBox "There is currently a file open with the name: " & vbCrLf & _
                     sFilename & vbCrLf & _
                    "Please close or rename open file! " _
                    , vbOKOnly + vbExclamation, "DUPLICATE NAME WARNING"
        Case Else
            MsgBox "Error No. " & Err.NUMBER & vbCrLf & "Description: " & Err.DESCRIPTION, vbExclamation, "Database Error"
            Err.Clear
            Resume Error_Handler_Exit
    End Select


End Sub

Sorry, this line should not have been commented out:

oSheet.Range("A1", oSheet.Range("W" & oSheet.Rows.Count).End(xlUp)).Sort oSheet.Range("E2"), 2, 1

That said, I don't think I left off the "xlUp". Please correct me if I am incorrect.

I have the "On Error Resume Next" only when creating the excel instance, would that error handler have any effect on any subsequent code?
If I run all the code with all the error handler (aside from the on error resume next) commented out, it runs and gives the same effect. Runs, appends data, but does not do the sort the table.

Good to know. Most of the research I did at the time had it written that way.
I have rewritten it as follows, creating the object first:

'Open an instance of Excel
    'On Error Resume Next
    Set excelApp = CreateObject("Excel.Application")
    

    If Err.NUMBER <> 0 Then
    Err.Clear
    'On Error GoTo Error_Handler
        Set excelApp = GetObject(, "Excel.Applicationn")
    End If
    Debug.Print "Excel Instance Created"

This works and I have made a little progress. I am now getting a runtime error 9 on the following line:

With oSheet.ListObjects("DETAIL_2020")

Not sure why, but at least the issue presented itself. The on error resume next was masking this error.

Should I replace the xlUp with "-4162" in the line:

oSheet.Range("A1", oSheet.Range("W" & oSheet.Rows.Count).End(xlUp)).Sort oSheet.Range("E2"), 2, 1