Export Recordset to Excel

[Darci]

Is there any way I can export a recordset in a subform to excel without creating a new object? I have a form that is opened with a docmd that passes a where clause. I want the user to be able to click a button to export what they are looking at in the subform data sheet to excel.

Thanks

 

[JerryPorter]

Darci,

I had the same question - here's how I did it today. I wrote code to open the subform as a separate (hidden) form. Then use the OutputTo method on that. The code is below - call it from a button on the form. Replace "SubFormControlName" by the name of your subform control.

Jerry

Private Sub ExportSubform()

    Dim frmSub As Form
    Dim frmExport As Form
    
    ' Get form object for subform
    Set frmSub = Me!SubFormControlName.Form
    
    ' Open subform hidden and get form object
    DoCmd.OpenForm frmSub.Name, , , , , acHidden
    Set frmExport = Forms(frmSub.Name)
    
    ' Copy filter and sort from subform
    frmExport.RecordSource = frmSub.RecordSource    'In case code changed recordsource
    frmExport.Filter = frmSub.Filter
    frmExport.FilterOn = frmSub.FilterOn
    frmExport.OrderBy = frmSub.OrderBy
    frmExport.OrderByOn = frmSub.OrderByOn
        
    ' Export
    DoCmd.OutputTo acOutputForm, frmExport.Name
        
    '   Close hidden form
    DoCmd.Close acForm, frmSub.Name

End Sub