Export filtered datasheet recordset to Excel

[MightyCat]

I've been trying to find examples on how to grab the recordset after a user has filtered a datasheet and export it to EXCEL with VBA from a Button on a form.

I've figured out how to export a pre-defined query to excel with all the fields I need. I was hoping to be able to grab the filters from the datasheet form and pass them to the query.

I have a main form with a bound Datasheet subform. The export button is on the main form and I need to grab the filtered data from the datahsheet subform.

Has anyone done this?

 

[MightyCat]

I've gotten a little further in that I can clone the recordset but it's saving the full recordset instead of the filtered.

I'm using the DoCmd.TransferSpreadsheet and have tried putting the rst in as the source and also rst.Name.

Using rst gives me "An expression you entered is the wrong data type for one of the arguments"

when I use rst.Name, it works but gives me the full recordset.

The code is below

Public Function GrabFilteredRecords(nameStr As String)

    
    
    
    On Error GoTo errHandle
    
        Dim fileDump As FileDialog
        Dim dd As Integer
        Dim rst As Recordset
        
        Set rst = Me.Recordset.Clone
      
        Set fileDump = Application.FileDialog(msoFileDialogSaveAs)
        
        fileDump.InitialFileName = nameStr & ".xls"
        dd = fileDump.Show
        
        If dd = -1 Then
            DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, rst, fileDump.SelectedItems(1), 1
            MsgBox "File has been exported", vbInformation
        Else
             MsgBox "EXPORT WAS NOT SUCCESSFUL !!" & vbCrLf & vbCrLf & "Please perform a manual EXPORT", vbCritical
        End If
        
        Set rst = Nothing
        
exitOnErr:
        Set rst = Nothing
        Exit Function
errHandle:
        MsgBox "Error occured, process terminated" & vbCrLf & vbCrLf & _
               "Error (" & Err.Number & ") : " & Err.Description, vbCritical
        Resume exitOnErr
        
        

End Function