Results 1 to 9 of 9

How to Export subform Recordset to Excel

  1. #1
    ezybusy is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Nov 2015
    Posts
    85

    How to Export subform Recordset to Excel

    Hi All,
    I have a Main form with 4 fields used to filter records on a subform. The subform recordsource is a query. Now i am trying to export to excel and pdf whatever results i get after filtering the subform.

    Example: Assume that i have 500 records in total before applying the filter. Then get 20 records after applying the filter. I want to have a button which i will click and then export only those 20 records to excel. And have another button to export to PDF.

    Can someone help me with the vba code please?

    I have attached a picture of my form for you to understand what i mean.

    Below is the recordsource of the subform.



    Code:
    Dim SQL As String
    Private Sub CmdSearch_Click()
    SQL = "SELECT SALE_TRANSACTION_HISTORY.Sale_Date, SALE_TRANSACTION_HISTORY.Item_Category, SALE_TRANSACTION_HISTORY.Item_Name, SALE_TRANSACTION_HISTORY.Item_Price, SALE_TRANSACTION_HISTORY.QtyOrdered, [Item_Price]*[QtyOrdered] AS Total, SALE_TRANSACTION_HISTORY.Item_Category_ID " _
        & " FROM SALE_TRANSACTION_HISTORY" _
        & " WHERE Sale_Date >= #" & Format(CDate(Me.TxtStartPeriod), "mm/dd/yyyy") & "# And Sale_Date <= #" & Format(CDate(Me.TxtEndPeriod), "mm/dd/yyyy") & "#" _
        & " AND Item_Name LIKE '*" & Me.TxtItem & "*'" _
        & " AND SALE_TRANSACTION_HISTORY.Item_Category LIKE '*" & Me.CboItemCategory.Column(1) & "*'" _
        & " ORDER BY SALE_TRANSACTION_HISTORY.Sale_Date DESC"
    
    
        Me.SubSalesReport.Form.RecordSource = SQL
        Me.SubSalesReport.Form.Requery
    End Sub

    Click image for larger version. 

Name:	Capture.PNG 
Views:	24 
Size:	40.4 KB 
ID:	34441

  2. #2
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    6,797
    use the query the subform uses to export. (make sure the query uses the key on the master form)

    vFile = "c:\folder\myfile.xlsx"
    tabname = "my Data"
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "qsData2Export", vFile, True,tabname

  3. #3
    Ajax is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    6,006
    you've got your sql, create a temporary querydef and export that. can't remember the exact code but something along the lines of

    Code:
    dim qdef as dao.querydef
    set qdef=currentdb.createquerydef("tmpQry",SQL)
    docmd.outputto acquery,"tmpqry",.....
    docmd.deleteobject acquery, "tmpqry"
    google/bing outputto to find all the parameters including things like filename/type

  4. #4
    ezybusy is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Nov 2015
    Posts
    85
    ok. Thanks Guys.
    Will try the provided codes and post back.

  5. #5
    Gicu is offline Expert
    Windows 8 Access 2013
    Join Date
    Jul 2015
    Posts
    513
    You are not actually "filtering" the data using the four controls, but you are applying criteria to the query (query by form). They are two different things and exporting them to Excel (filtered query vs. query by form) involves different steps. In your case save the sql string that you use as the recordsource for the subform as a query and use ranman's code to export to Excel. For the PDF export it is best to create a new Access report based on the same query, make it look pretty then use Docmd.Outputto acreport, "YourReport",acFormatPDF,YourPDFFileName.

    Cheers,
    Vlad

  6. #6
    ezybusy is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Nov 2015
    Posts
    85
    Guys am not able to get it. Can someone please help me with the full code to export the filtered records in excel file?

  7. #7
    ezybusy is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Nov 2015
    Posts
    85
    Guys no one to help me out?

  8. #8
    Gicu is offline Expert
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Posts
    513
    I don't think is much more to help you with unless your post a sample file with your form. Have you tried to create the report, can you show any VBA code that you tried and failed along with the errors you got.

    Cheers,
    Vlad

  9. #9
    ezybusy is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Nov 2015
    Posts
    85
    Found the answer.
    For those looking to do the same, paste the following code in the onclik event of a command:

    Code:
        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.


    https://www.experts-exchange.com/que...-to-Excel.html

Please reply to this thread with any new information or opinions.

Similar Threads

  1. VBA To Export Recordset To Excel
    By jo15765 in forum Programming
    Replies: 9
    Last Post: 11-13-2017, 10:27 PM
  2. Replies: 3
    Last Post: 05-17-2017, 09:45 AM
  3. Replies: 3
    Last Post: 03-05-2015, 03:15 PM
  4. export data from subform to excel
    By raffi in forum Import/Export Data
    Replies: 2
    Last Post: 11-02-2014, 02:40 PM
  5. Export Recordset to Excel
    By bimcompu in forum Programming
    Replies: 1
    Last Post: 01-08-2014, 04:53 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Tech Forums: Microsoft Office Forums