Results 1 to 4 of 4
  1. #1
    Jhail83 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2013
    Posts
    8

    Sort Columns in an excel worksheet and Pivot table

    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

  2. #2
    Jhail83 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2013
    Posts
    8
    Quote Originally Posted by Jhail83 View Post
    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
    Any ideas how to sort in an excel sheet by specific column, through VBA, in access?

  3. #3
    Jhail83 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2013
    Posts
    8
    If anyone can at least point me in the right direction, i'd appreciate it.

  4. #4
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows XP Access 2003
    Join Date
    Aug 2013
    Posts
    7,862
    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.....

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Importing Excel - Update table field with the worksheet name
    By maggiemago3 in forum Import/Export Data
    Replies: 1
    Last Post: 08-22-2013, 04:51 PM
  2. Replies: 2
    Last Post: 01-23-2013, 04:57 PM
  3. Replies: 1
    Last Post: 11-15-2012, 09:16 AM
  4. Replies: 3
    Last Post: 08-22-2012, 06:28 AM
  5. Replies: 1
    Last Post: 11-21-2010, 10:26 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums