Sort Columns in an excel worksheet and Pivot table

[Jhail83]

I am copying a query to an excel workbook.



I want to sort the columns on the worksheet where this information is going.

I want to sort one column, then another. (To line it up correctly)

Also, there are pivot tables that utilize this information.

Can I also sort the pivot tables in different worksheets in the same workbooks?

Here is my code, and I bolded Pseudocode about what I want to accomplish.

Public Sub btn_Export_Click()
Dim objXL As Object
Dim xlWB As Object
Dim xlWS As Object
Dim rst As DAO.Recordset
Dim rst2 As DAO.Recordset
Dim strComputer As String
Dim strProcessKill As String
Dim objWMIService As Object
Dim colProcess As Object

'Close all instances of excel to make sure that template we are writing to, or file we are saving to are not open.
strComputer = "."
strProcessKill = "'excel.exe'"
Set objWMIService = GetObject("winmgmts:" & "{impersonationLevel=impersonate}!\\" & strComputer & "\root\cimv2")
Set colProcess = objWMIService.ExecQuery("Select * from Win32_Process Where Name = " & strProcessKill)
For Each objProcess In colProcess
objProcess.Terminate
Next
'WSCript.Echo "Just killed process " & strProcessKill & " on " & strComputer
'WScript.Quit
'End of WMI Example of a Kill Process

'Open Excel Template and write to each tab we need to insert data to.
Set objXL = CreateObject("Excel.Application")

Set xlWB = objXL.Workbooks.Open(CurrentProject.Path & "\DEA Budget Template")
'---------------------------------------------------------------------------------------------------------------------

Set xlWS = xlWB.Worksheets("Budget Detail")

Set rst = CurrentDb.OpenRecordset("qry_AllDivBudgetOutput")

xlWS.Range("B3").CopyFromRecordset rst

'Sort Column E then B, Headers are in 2nd row.

'---------------------------------------------------------------------------------------------------------------------

Set xlWS = xlWB.Worksheets("Chart of Accounts")

Set rst = CurrentDb.OpenRecordset("qry_Accounts")

xlWS.Range("B3").CopyFromRecordset rst

'---------------------------------------------------------------------------------------------------------------------

Set xlWS = xlWB.Worksheets("203700 per Store")

Set rst = CurrentDb.OpenRecordset("qry_203700 per Store")

xlWS.Range("B3").CopyFromRecordset rst

'---------------------------------------------------------------------------------------------------------------------

'Sort by 'manager' field in the pivot table in the "Account by Mgr" worksheet
'Sort by 'vendor' field in the pivot table in the "Software by Account" worksheet
'Sort by 'Row Labels' field in the pivot table in the "Metrics by Division" worksheet.


'---------------------------------------------------------------------------------------------------------------------

xlWB.RefreshAll

On Error GoTo ErrorHandle
xlWB.SaveAs CurrentProject.Path & "\" & Year(Date) & " DEA Budget Report.xlsx"

MsgBox "Report saved as " & CurrentProject.Path & "\" & Year(Date) & " DEA Budget Report.xlsx"

xlWB.Application.Visible = True

Set fd = Nothing
rst.Close
Set rst1 = Nothing
Exit Sub

ErrorHandle:
'Close all instances of excel to make sure that template we are writing to, or file we are saving to are not open.
strComputer = "."
strProcessKill = "'excel.exe'"
Set objWMIService = GetObject("winmgmts:" & "{impersonationLevel=impersonate}!\\" & strComputer & "\root\cimv2")
Set colProcess = objWMIService.ExecQuery("Select * from Win32_Process Where Name = " & strProcessKill)
For Each objProcess In colProcess
objProcess.Terminate
Next

Set fd = Nothing
rst.Close
Set rst1 = Nothing

End Sub

[Jhail83]

I am copying a query to an excel workbook.

I want to sort the columns on the worksheet where this information is going.

I want to sort one column, then another. (To line it up correctly)

Also, there are pivot tables that utilize this information.

Can I also sort the pivot tables in different worksheets in the same workbooks?

Here is my code, and I bolded Pseudocode about what I want to accomplish.

Public Sub btn_Export_Click()
Dim objXL As Object
Dim xlWB As Object
Dim xlWS As Object
Dim rst As DAO.Recordset
Dim rst2 As DAO.Recordset
Dim strComputer As String
Dim strProcessKill As String
Dim objWMIService As Object
Dim colProcess As Object

'Close all instances of excel to make sure that template we are writing to, or file we are saving to are not open.
strComputer = "."
strProcessKill = "'excel.exe'"
Set objWMIService = GetObject("winmgmts:" & "{impersonationLevel=impersonate}!\\" & strComputer & "\root\cimv2")
Set colProcess = objWMIService.ExecQuery("Select * from Win32_Process Where Name = " & strProcessKill)
For Each objProcess In colProcess
objProcess.Terminate
Next
'WSCript.Echo "Just killed process " & strProcessKill & " on " & strComputer
'WScript.Quit
'End of WMI Example of a Kill Process

'Open Excel Template and write to each tab we need to insert data to.
Set objXL = CreateObject("Excel.Application")

Set xlWB = objXL.Workbooks.Open(CurrentProject.Path & "\DEA Budget Template")
'---------------------------------------------------------------------------------------------------------------------

Set xlWS = xlWB.Worksheets("Budget Detail")

Set rst = CurrentDb.OpenRecordset("qry_AllDivBudgetOutput")

xlWS.Range("B3").CopyFromRecordset rst

'Sort Column E then B, Headers are in 2nd row.

'---------------------------------------------------------------------------------------------------------------------

Set xlWS = xlWB.Worksheets("Chart of Accounts")

Set rst = CurrentDb.OpenRecordset("qry_Accounts")

xlWS.Range("B3").CopyFromRecordset rst

'---------------------------------------------------------------------------------------------------------------------

Set xlWS = xlWB.Worksheets("203700 per Store")

Set rst = CurrentDb.OpenRecordset("qry_203700 per Store")

xlWS.Range("B3").CopyFromRecordset rst

'---------------------------------------------------------------------------------------------------------------------

'Sort by 'manager' field in the pivot table in the "Account by Mgr" worksheet
'Sort by 'vendor' field in the pivot table in the "Software by Account" worksheet
'Sort by 'Row Labels' field in the pivot table in the "Metrics by Division" worksheet.


'---------------------------------------------------------------------------------------------------------------------

xlWB.RefreshAll

On Error GoTo ErrorHandle
xlWB.SaveAs CurrentProject.Path & "\" & Year(Date) & " DEA Budget Report.xlsx"

MsgBox "Report saved as " & CurrentProject.Path & "\" & Year(Date) & " DEA Budget Report.xlsx"

xlWB.Application.Visible = True

Set fd = Nothing
rst.Close
Set rst1 = Nothing
Exit Sub

ErrorHandle:
'Close all instances of excel to make sure that template we are writing to, or file we are saving to are not open.
strComputer = "."
strProcessKill = "'excel.exe'"
Set objWMIService = GetObject("winmgmts:" & "{impersonationLevel=impersonate}!\\" & strComputer & "\root\cimv2")
Set colProcess = objWMIService.ExecQuery("Select * from Win32_Process Where Name = " & strProcessKill)
For Each objProcess In colProcess
objProcess.Terminate
Next

Set fd = Nothing
rst.Close
Set rst1 = Nothing

End Sub

Any ideas how to sort in an excel sheet by specific column, through VBA, in access?

[Jhail83]

If anyone can at least point me in the right direction, i'd appreciate it.

[ItsMe]

Not a perfect solution but I would approach it this way. Just some food for thought.

You could build a separate form and on that form use the tab index to list the fields in the order you want. From there you can use the DoCmd to open the form and export to Excel. The one drawback is the screen flickers while it opens and closes the form. You can try and hide the form within the code. Here is some sample code. You can even call the form the user is currently viewing by naming the form literally. You can include a variable to pass along a user’s filter from a viewed form to the exported file.

DoCmd.OpenForm stDocName, acNormal, , varWhere, acFormReadOnly
DoCmd.SelectObject acForm, stDocName
DoCmd.OutputTo acOutputForm, stDocName, acFormatXLS
DoCmd.Close acForm, stDocName

This code will delete and overwrite any similarly named Excel file. If you need to append (Like for a pivot table) the Excel file then you may need to use an append query that stores the data in a separate table before exporting. You should not have a problem with the field order after it is in another table.

Got to go to the dentist now.....