none
Export Filtered Subform Data to Excel

    Question

  • Hello,

    I have a 2016 MS Access database called WIP Customer Services on a Win7 machine. The database has a form named form_WIP. This form contains a subform named subform_WIP. This subform obtains its data from a table named tblWIP.

    When the end user applies a filter to the subform, I want them to be able to export the filtered data on the subform to Excel. How can this be done? The subform contains 16 fields that can have filters applied.

    Any suggestion is appreciated.

    Thank you, Kevin

    Wednesday, May 22, 2019 1:27 PM

Answers

  • Hi DBguy,

    I did a little research on a different approach. I found a sub that uses record set cloning. And it works great!

    Thank you both for your suggestions. Below is the code I am utilizing.

    Private Sub Command0_Click()
    
    If Me.Dirty Then Me.Dirty = False
    
    Dim rsClone As DAO.Recordset
    Set rsClone = Me.SUBFORM_NAME.Form.RecordsetClone
    
    If rsClone.EOF Then
    MsgBox "No records found."
    Set rsClone = Nothing
    Exit Sub
    End If
    
        Dim xlApp As Object
        Set xlApp = CreateObject("Excel.Application")
        With xlApp
            .Visible = True
            .Workbooks.Add
            .Sheets("Sheet1").Select
            
            .ActiveSheet.Range("A2").CopyFromRecordset rsClone
            
            For i = 1 To rsClone.Fields.Count
                xlApp.ActiveSheet.Cells(1, i).Value = rsClone.Fields(i - 1).Name
            Next i
            xlApp.Cells.EntireColumn.AutoFit
        End With
        
        
    End Sub

    • Marked as answer by KevinATF Wednesday, May 22, 2019 4:53 PM
    Wednesday, May 22, 2019 4:52 PM

All replies

  • Create a Select query based upon your filtered subform data and give the query a name such as 'ExportDataQuery'. Then export the query data using the DoCmd.TransferSpreadsheet command. You can insert the command at the end of the VBA code that filters the data or create a command button that runs the command when the user clicks it on the form.
    Wednesday, May 22, 2019 2:03 PM
  • Hi Lawrence,

    Thanks for your reply. Can you give me a example of what I need to add to the Criteria of my selectquery named 'ExportDataQuery'?

    Cheers, Kevin

    Wednesday, May 22, 2019 2:09 PM
  • In each fields Criteria box put:

    Forms![form_WIP]![subform_WIP]![subform_WIP control name]

    For example, say your subform_WIP has a control that is named 'CustomerAddress' then the criteria box for the CustomerAddress field would be:

    Forms![form_WIP]![subform_WIP]![CustomerAddress]

    Understand that I am NOT talking about each controls Control Source property. I am talking about the controls Name property. Those two properties could be the same or they may not.

    Wednesday, May 22, 2019 2:55 PM
  • I tried your suggestion. However, when no filters have been applied, the 'ExportDataQuery' only returns the first record. Shouldn't it return all the records?

    Wednesday, May 22, 2019 3:29 PM
  • Hi Kevin. But does it work if you had filters applied to the form/subform? I thought the syntax required the Form part. For example: Forms!MainFormName.SubformName.Form!TextboxName

    Anyway, if it does work when there's filter applied, then to also make it work when there's no filter applied, you'll have to add an OR criteria. For example: Forms!MainFormName.SubformName.Form!TextboxName OR Forms!MainFormName.SubformName.Form!TextboxName Is Null

    Hope it helps...

    Wednesday, May 22, 2019 4:40 PM
  • Hi DBguy,

    I did a little research on a different approach. I found a sub that uses record set cloning. And it works great!

    Thank you both for your suggestions. Below is the code I am utilizing.

    Private Sub Command0_Click()
    
    If Me.Dirty Then Me.Dirty = False
    
    Dim rsClone As DAO.Recordset
    Set rsClone = Me.SUBFORM_NAME.Form.RecordsetClone
    
    If rsClone.EOF Then
    MsgBox "No records found."
    Set rsClone = Nothing
    Exit Sub
    End If
    
        Dim xlApp As Object
        Set xlApp = CreateObject("Excel.Application")
        With xlApp
            .Visible = True
            .Workbooks.Add
            .Sheets("Sheet1").Select
            
            .ActiveSheet.Range("A2").CopyFromRecordset rsClone
            
            For i = 1 To rsClone.Fields.Count
                xlApp.ActiveSheet.Cells(1, i).Value = rsClone.Fields(i - 1).Name
            Next i
            xlApp.Cells.EntireColumn.AutoFit
        End With
        
        
    End Sub

    • Marked as answer by KevinATF Wednesday, May 22, 2019 4:53 PM
    Wednesday, May 22, 2019 4:52 PM
  • Hi Kevin. Glad to hear you found a solution. Good luck!
    Wednesday, May 22, 2019 4:56 PM
  • Your query will reurn all records where the 'Wave' field in WIP are equal to the ACTIVE CURRENT RECORD Forms![form_WIP]![subform_WIP]![Wave] field.

    For example, if the active current record 'Wave' field is a True or False field AND Forms![form_WIP]![subform_WIP]![Wave]=True, then it will return whatever records where 'Wave' = True based upon Forms![form_WIP]![subform_WIP]![Wave].

    If you 16 different fields that can be filtered with 16 different combinations...well you can do the math if you want to. You have a considerable challenge even if you used VBA and the DoCmd.ApplyFilter command or the Me.Fllter command.

    Wednesday, May 22, 2019 6:10 PM