Exporting Data a Microsoft Access from a subform to Excel

Posted on 2006-05-26
Last Modified: 2008-02-01
I have a search form with a subform on it.  I want to export the results from the subform to Microsoft excel.  The subform is displayed as a datasheet.  I am using the DoCmd.OutputTo acOutputForm, "subDepositions2", acFormatXLS command but it is not exporting the data that is displayed in the subform from the search results but the underlying query of the subform.  Can anybody help me with this?

Question by:wilcon
    LVL 27

    Expert Comment

    it is the data that is being exported not the look of the subform,
    so the command is working properly.

    if you want to export the same design and data, then you can do a Report, and export the Report, using the same query of the subform.

    LVL 58

    Accepted Solution

    Hello wilcon

    Try this as event handler of a command button:

    Private Sub cmdTest_Click()

        Dim XL As Excel.Application
        Dim xlrngCell As Excel.Range
        Dim rs As DAO.Recordset
        Dim intF As Integer
        On Error Resume Next
        Set XL = GetObject(, "Excel.Application")
        If XL Is Nothing Then
            Set XL = CreateObject("Excel.Application")
            If XL Is Nothing Then
                MsgBox "Can't find Excel!", vbCritical
                Exit Sub
            End If
            XL.Visible = True
            XL.UserControl = True
        End If

        Set xlrngCell = XL.Workbooks.Add.Worksheets(1).Range("A1")
        Set rs = Me.XXXXX.Form.RecordsetClone
        For intF = 0 To rs.Fields.Count - 1
            xlrngCell(, intF + 1) = rs.Fields(intF).Name
        Next intF
        xlrngCell.Offset(1).CopyFromRecordset rs
        xlrngCell.Worksheet.Parent.Saved = True

    End Sub

    Replace XXXXX with the name of the subform control containing the subform you want to export.

    Good luck!
    LVL 3

    Expert Comment


    How have you linked the form and the sub form? If you are using the Master and Child properties then these will not be passed to the function.

    I would suggest that you create a Select query and pass the filter parameters from the main form to this and then use this query as the data source for the export.

    ie QueryABC :Select "*" From subDepositions2 where subDepositions2.field1 = form.filter1 and subDepositions2.field2 = form.filter2

    DoCmd.OutputTo acOutputForm, "querty ABC", acFormatXLS

    Try this.


    Author Comment

    thank you very much harfang.
    LVL 58

    Expert Comment

    Welcome! Glad you liked it.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    In the previous article, Using a Critera Form to Filter Records (, the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
    I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
    In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
    With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

    760 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    7 Experts available now in Live!

    Get 1:1 Help Now