Results 1 to 10 of 10
  1. #1
    jdashm is offline Advanced Beginner
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2016
    Posts
    58

    Export filtered form data to excel workbook

    I have a sub-form based on a union query so that users can filter data. I am trying to export just that filtered data to an Excel workbook.
    I have code that will export but always exports the entire dataset.
    How can i limit just the filtered records in this code...
    ================================================== =====================================
    Public Function ExportDocument_Agreements() As TaskExportEnum
    ' Runs Filtered Agreements Export Process using folder location and file name flexibility


    On Error GoTo ErrProc

    Dim fd As FileDialog
    Set fd = Application.FileDialog(msoFileDialogSaveAs)



    With fd
    .InitialFileName = "Results"
    .Title = "Export To Excel"
    .ButtonName = " Save Export "
    .AllowMultiSelect = False 'Change this to TRUE to enable multi-select


    'If aborted, the Function will return the default value of Aborted
    If .Show = 0 Then GoTo Leave
    End With


    Dim SelectedItem As Variant
    For Each SelectedItem In fd.SelectedItems

    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "qunAgreementsAndArchives", fd.InitialFileName, True



    Next SelectedItem




    'Return Success
    ExportDocument_Agreements = TaskExportEnum.Success


    Leave:
    Set fd = Nothing
    On Error GoTo 0
    Exit Function


    ErrProc:
    MsgBox Err.Description, vbCritical
    ExportDocument_Agreements = TaskExportEnum.Failure 'Return Failure if error
    Resume Leave
    End Function
    ================================================== =======================

    Any assistance would be appreciated.

  2. #2
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Please try this (you will need to create a new query named qryX_Filtered based on the original one):
    Code:
    
    Dim SelectedItem As Variant,strFilter as String,qdfSelected as DAO.QueryDef,strSQL as String
    
    
    For Each SelectedItem In fd.SelectedItems
    
    
    'DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "qunAgreementsAndArchives", fd.InitialFileName, True
    
    
    strFilter = Nz(Forms!YourForm!Subform.Form.Filter,"") 'here you reference your filtered subform
    
    
    Set qdfSelected = CurrentDb.QueryDefs("qryX_Filtered")
    If strFilter = "" Then
        qdfSelected.SQL= "SELECT qunAgreementsAndArchives.* FROM qunAgreementsAndArchives;"
    Else
        qdfSelected.SQL= "SELECT qunAgreementsAndArchives.* FROM qunAgreementsAndArchives WHERE " & strFilter & ";"
    End If
    
    
    Next SelectedItem
    
    
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "qryX_Filtered",fd.InitialFileName, True
    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  3. #3
    jdashm is offline Advanced Beginner
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2016
    Posts
    58
    This worked perfectly. Many thanks!

  4. #4
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    You're very welcome!
    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  5. #5
    jdashm is offline Advanced Beginner
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2016
    Posts
    58
    Vlad...

    An issue has arisen to your solution from yesterday in that the system is not clearing the QueryDefs query "qryX_Filtered" even after i clear all filters from the form.
    Works okay if i change the criteria but it retains previous criteria when form is opened.
    Any assistance would be appreciated.

    Here is the current code I have...
    ================================================== ================================================== ====
    ' Runs Filtered Agreements Export Process using folder location and file name flexibility


    On Error GoTo ErrProc

    Dim fd As FileDialog
    Set fd = Application.FileDialog(msoFileDialogSaveAs)
    Dim SelectedItem As Variant, strFilter As String, qdfSelected As DAO.QueryDef, strSQL As String


    '---------------------------------------------------------------------------------------
    ' Displays Save As Dialog Box
    With fd
    .InitialFileName = InputBox("Enter the file name to save export as")
    .Title = "Export To Excel"
    ' With .Filters
    ' .Clear
    ' .Add "Excel documents", "*.xlsx", 1
    ' End With
    .ButtonName = " Save Export "
    .AllowMultiSelect = False 'Change this to TRUE to enable multi-select



    'If aborted, the Function will return the default value of Aborted
    If .Show = 0 Then GoTo Leave
    End With
    '---------------------------------------------------------------------------------------


    ' Runs query for filtered items in report form
    For Each SelectedItem In fd.SelectedItems

    strFilter = Nz(Forms!frmAgrmntReporting_All_Admin!fsb_qunAgree mentsAndArchives_Admin.Form.Filter, "") 'here you reference your filtered subform

    Set qdfSelected = CurrentDb.QueryDefs("qryX_Filtered")

    If strFilter = "" Then
    qdfSelected.SQL = "SELECT qunAgreementsAndArchives.* FROM qunAgreementsAndArchives;"
    Else
    qdfSelected.SQL = "SELECT qunAgreementsAndArchives.* FROM qunAgreementsAndArchives WHERE " & strFilter & ";"
    End If


    Next SelectedItem

    Debug.Print strFilter


    'Performs Export process
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "qryX_Filtered", fd.InitialFileName, True

    ' DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "qunAgreementsAndArchives", fd.InitialFileName, True

    ' DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12Xml, "ImportNewStudents", SelectedItem, True
    'Change 'YourTableName' and 'YourSheetName' to the actual names




    'Return Success
    ExportDocument_Agreements = TaskExportEnum.Success


    Leave:
    Set fd = Nothing
    On Error GoTo 0
    Exit Function


    ErrProc:
    MsgBox Err.Description, vbCritical
    ExportDocument_Agreements = TaskExportEnum.Failure 'Return Failure if error
    Resume Leave
    End Function
    ================================================== ====================================

  6. #6
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    I think it is built in Access functionality to keep the filter if Filter On Load is set to True. In the Open event of the form you can reset the filter on the subform:
    Me.fsb_qunAgree mentsAndArchives_Admin.Form.Filter="".

    Hope I got what you are saying.

    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  7. #7
    jdashm is offline Advanced Beginner
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2016
    Posts
    58
    That seems to work except i had to reference the entire form not just the subform...

    Forms!frmAgrmntReporting_All_Admin!fsb_qunAgreemen tsAndArchives_Admin.Form.Filter = ""

    One more question if you are up for it... how can i get Excel to automatically open the exported file?

    Many thanks.

  8. #8
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    You can try to replace the DoCmd.TransferSpreadsheet with Docmd.OutputTo (with acFormatXLS) which has an AutoStart parameter you would set to True, or after the DoCmd.TransferSpreadsheet line call a ShelExecute (there are many variants on the web depending on your "bitness") 32 vs.64) to open the file.

    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  9. #9
    jdashm is offline Advanced Beginner
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2016
    Posts
    58
    Great and again many thanks for your help.

  10. #10
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Good luck with your project !



    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

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

Similar Threads

  1. Export Filtered Form with associated Subform results to excel
    By mindbender in forum Import/Export Data
    Replies: 17
    Last Post: 02-14-2019, 07:04 PM
  2. Export Filtered Form to Excel
    By wizzz_wizzz in forum Import/Export Data
    Replies: 2
    Last Post: 11-24-2016, 07:21 PM
  3. Replies: 1
    Last Post: 10-26-2016, 06:38 AM
  4. Replies: 2
    Last Post: 10-15-2014, 12:30 PM
  5. Export Filtered Query to Excel Using Combo Box on Form
    By besuchanko in forum Import/Export Data
    Replies: 0
    Last Post: 02-07-2013, 10:10 PM

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
  •  
Other Forums: Microsoft Office Forums