• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1353
  • Last Modified:

Exporting Data a Microsoft Access from a subform to Excel

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?

0
wilcon
Asked:
wilcon
1 Solution
 
jjafferrCommented:
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.

jaffer
0
 
harfangCommented:
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
    rs.MoveFirst
    xlrngCell.Offset(1).CopyFromRecordset rs
   
    xlrngCell.Worksheet.Cells.EntireColumn.AutoFit
    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!
(°v°)
0
 
RohitPattniCommented:
Hi

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.

Rohit
0
 
wilconAuthor Commented:
thank you very much harfang.
0
 
harfangCommented:
Welcome! Glad you liked it.
(°v°)
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now