Results 1 to 8 of 8
  1. #1
    Johnny12 is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Dec 2012
    Posts
    31

    Freeze Row and Add Sort

    Good morning everyone! I have a question regarding adding a Freeze Row and Sort function to an Excel Report exported from Access. My report is built and been running great for weeks. Of course now the boss wants me to Freeze the headers (which are in Row 4) and add sorting to the columns. Currently I have to manually do it after I have exported the Excel report from Access. Is there a way to do this in the same script I use to build/export this report? below is a snippet of what the exported report looks like in Excel. In the picture, I've already added the sorting ability manually.


    Thanks!

    Click image for larger version. 

Name:	Capture22.PNG 
Views:	34 
Size:	8.0 KB 
ID:	43008


  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Are you using automation to export? One trick I've used is to record a macro in Excel while you manually do what you want, and then adapt the resulting code to work from Access.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    Join Date
    Jun 2010
    Location
    Belgium
    Posts
    1,035
    you can try something like

    Code:
    Public Sub OpenExcel(strFileName As String, strQueryName As String)
    On Error GoTo Err_OpenExcel
    
    
        Dim appExcel As Excel.Application
        Dim wkbBooks As Excel.Workbooks
        Dim wkbBook As Excel.Workbook
        Dim wksSheet As Excel.Worksheet
    
    
        
       DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, strQueryName, strFileName, True
        
        Set appExcel = GetObject(, "Excel.Application")
          
        Set wkbBooks = appExcel.Workbooks
        Set wkbBook = wkbBooks.Add(strFileName)
        Set wksSheet = wkbBook.Sheets(strQueryName)
        wksSheet.Activate
         With appExcel.ActiveWindow
            .SplitColumn = 0
            .SplitRow = 4
        End With
        appExcel.ActiveWindow.FreezePanes = True
        appExcel.ActiveSheet.Range("A4").CurrentRegion.autofilter
        wkbBook.Save
        appExcel.Visible = True
    
    
    Exit_OpenExcel:
        Exit Sub
        
    Err_OpenExcel:
        If Err.Number = 429 Then 'excel is not running
            Set appExcel = CreateObject("excel.application")
            Resume Next
        Else
            MsgBox Err.Number & ": " & Err.Description
        End If
        Resume Exit_OpenExcel
    
    
    End Sub
    Don't forget to activate the Microsoft Excel Object library in the VBA -> references window

  4. #4
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Quote Originally Posted by NoellaG View Post
    Don't forget to activate the Microsoft Excel Object library in the VBA -> references window
    Or use late binding.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    Johnny12 is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Dec 2012
    Posts
    31
    Quote Originally Posted by pbaldy View Post
    Are you using automation to export? One trick I've used is to record a macro in Excel while you manually do what you want, and then adapt the resulting code to work from Access.

    Sorry for the late response but wanted to let you know your comment took me to half the answer. This did the trick for freezing it where I wanted. I still have not found a solution to add sorting to the excel document that is created by the export.



    Code:
       ActiveWindow.FreezePanes = False   Rows("5:5").Select
       ActiveWindow.FreezePanes = True

  6. #6
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    I've never tried to add sorting functionality in Excel after the export. Did the macro recording not give usable code?
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  7. #7
    Johnny12 is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Dec 2012
    Posts
    31
    @pbaldy I don;t know what I messed up the first time, but I rerecorded the macro and poof, it worked. Thanks!

    Code:
        Rows("1:1").Select
        Selection.AutoFilter

  8. #8
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Happy to help! Did you adapt it to use from Access? If you don't, you may end up with a hanging Excel process. See if there's an Excel process running in Task Manager after you're process is done and any Excel files are closed. As an example, here's a line from a process of mine:

    xl.Cells(R, C).Select
    xl.Selection.Interior.ColorIndex = 39

    Note I'm using the xl variable I previously set to the Excel instance.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

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

Similar Threads

  1. How to freeze parts of form?
    By Abacus1234 in forum Forms
    Replies: 9
    Last Post: 09-17-2016, 08:55 AM
  2. Freeze Header
    By Derrick T. Davidson in forum Reports
    Replies: 1
    Last Post: 07-13-2014, 03:26 AM
  3. Freeze panes in reports
    By Jamy in forum Reports
    Replies: 4
    Last Post: 01-02-2012, 03:19 PM
  4. Freeze Columns in a Form?
    By Paul H in forum Forms
    Replies: 9
    Last Post: 09-09-2011, 01:40 PM
  5. using VBA to freeze columns
    By bdaniel in forum Forms
    Replies: 1
    Last Post: 02-12-2010, 05:36 AM

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