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.
Code:
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