Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Export Recordset to Excel

Status
Not open for further replies.

Darci

IS-IT--Management
Jul 23, 2002
37
US
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
 
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

Code:
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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top