Results 1 to 8 of 8
  1. #1
    Join Date
    Aug 2015
    Posts
    7

    Unanswered: VBA for adding Autofilters to all Excel Exports

    I have the code for exporting 3 queries from Access to Excel. I'm looking for the piece of code to add autofilters to each of the Excel spreadsheets.

    Private Sub Export_CPS_Data_Click()
    strPath = "The path for the the Excel file.XLS"
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "Excel file name 1", strPath
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "Excel file name 2", strPath
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "Excel file name 3", strPath
    MsgBox "Export was successful"
    End Sub

  2. #2
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    495
    Provided Answers: 24
    NOTE: EXCEL must be in the VBE, menu, tools , references. in order to control XL.
    Code:
    Dim r As Long
    Dim xl As Excel.Application
    dim ws as worksheet
    
    Set xl = CreateObject("excel.application")
    With xl
        .Visible = True
        .Workbooks.Open strPath
    
       for each ws in .worksheets
         ws.select
        .Range("A1").Select
         r = .ActiveSheet.UsedRange.Rows.Count
        .Selection.AutoFilter
        
          'filter a field
        '.ActiveSheet.Range("$A$1:$D$" & r).AutoFilter Field:=1
    
       next 
    End With
    Set xl = Nothing

  3. #3
    Join Date
    Aug 2015
    Posts
    7

    Excellent...one final step

    Quote Originally Posted by ranman256 View Post
    NOTE: EXCEL must be in the VBE, menu, tools , references. in order to control XL.
    Code:
    Dim r As Long
    Dim xl As Excel.Application
    dim ws as worksheet
    
    Set xl = CreateObject("excel.application")
    With xl
        .Visible = True
        .Workbooks.Open strPath
    
       for each ws in .worksheets
         ws.select
        .Range("A1").Select
         r = .ActiveSheet.UsedRange.Rows.Count
        .Selection.AutoFilter
        
          'filter a field
        '.ActiveSheet.Range("$A$1:$D$" & r).AutoFilter Field:=1
    
       next 
    End With
    Set xl = Nothing
    Huge thanks in advance: Forgot to ask for:
    "Autofit all columns in all worksheets" code.

    Figured out the below but struggling with the above (autofit)
    .Worksheets(1).Select
    .Range("A1").Select
    .ActiveWorkbook.Save
    .ActiveWorkbook.Close
    xl.quit
    Last edited by dwright2; 08-05-15 at 19:26.

  4. #4
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    495
    Provided Answers: 24
    Code:
    dim ws as worksheet
    
    with xl
       for each ws in .worksheets
            ws.select
              '---now run the filter code here
       next
    
    .ActiveWorkbook.Save
    .ActiveWorkbook.Close
    xl.quit
    end with
    set ws = nothing

  5. #5
    Join Date
    Aug 2015
    Posts
    7

    Still struggling with autofit code

    This is what I have so far but don't understand where or how to call the "all worksheets all columns autofit"

    Private Sub Export_CPS_Data_Click()
    strPath = "The path for the the Excel file.XLS"
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "Excel file name 1", strPath
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "Excel file name 2", strPath
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "Excel file name 3", strPath

    Dim r As Long
    Dim xl As Excel.Application
    Dim ws As worksheet

    Set xl = CreateObject("excel.application")
    With xl
    .Visible = True
    .Workbooks.Open strPath

    For Each ws In .Worksheets
    ws.Select
    .Range("A1").Select
    r = .ActiveSheet.UsedRange.Rows.Count
    .Selection.AutoFilter

    Next
    .Worksheets(1).Select
    .Range("A1").Select
    .ActiveWorkbook.Save
    .ActiveWorkbook.Close
    xl.Quit

    End With
    Set xl = Nothing

    MsgBox "Export was successful"
    End Sub

  6. #6
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    495
    Provided Answers: 24
    after ws.select
    put in an autofit command

    Cells.Select
    Selection.EntireColumn.AutoFit

  7. #7
    Join Date
    Aug 2015
    Posts
    7

    One final question

    Well that was easier than I was trying to make it. This is what I came up with:

    For Each ws In .Worksheets
    ws.Select
    .Columns.Select
    r = .ActiveSheet.UsedRange.Rows.Count
    .Selection.AutoFilter
    .Selection.EntireColumn.AutoFit

    One final question (I promise)
    The first time the Excel file is created everything works as expected. However, once the Excel file exists and I re-run the export again, the autofilter command doesn't work. Does that have something to do with the "Set xl = CreateObject("excel.application")" line and if so is there a fix?

    Thanks---you've been very helpful and patient.

  8. #8
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    495
    Provided Answers: 24
    it could be
    .Selection.AutoFilter

    this is a toggle, if its on, it turns off, (and vise versa)
    but it shouldnt be on, if its a new document.
    (puzzling)

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •