Results 1 to 10 of 10
  1. #1
    Join Date
    Aug 2004
    Posts
    173

    Unanswered: Exporting Filtered Records from Subform to Excel

    I have a Main Form with a Subform (Datasheet) embedded inside. I would like to sort and filter the records in the Datasheet/Subform, and have a button on the Main Form that will allow me to export the Selected Records to Excel.

    Right now, I am usung a Macro to Output the Subform to Excel - it works, but it always exports ALL the Records and Not the Selected/Filtered ones.

    Is there a way to export only the selected records?

    Any help is appreciated - AB

  2. #2
    Join Date
    Nov 2003
    Location
    LONDON
    Posts
    238
    You could use code such as this (NOTE: you need a referece to ADO and Excel under VBA, References):

    Code:
      Private mobjXl As Excel.Application
    
    Private Function ExportToExcel(strSQL as String)
     Dim rst ADODB.Recordset
     Dim intCount As Integer
     ' Create the Excel object
     Set mobjXl = New Excel.Application
    
     ' Fetch the recordset
     With rst
    		 .ActiveConnection = CurrentProject.Connection.ConnectionString
    		 .CursorLocation = adUseClient
    		 .CursorType = adOpenForwardOnly
    		 .LockType = adLockReadOnly
    		 .MaxRecords = 65000 ' Approx max number of rows Excel can handle
    		 .Open strSQL		
     End With
    
      With mobjxl
     ' Add a workbook and turn of Excel updates
     .ScreenUpdating = False
    	 .Visible = False
    	 .Workbooks.Add
    	 .DisplayAlerts = False
    
     ' Add the column headers
    		For intCount = 0 To rst.Fields.COUNT - 1
    			.Cells(1, intCount + 1).Value = rst.Fields(intCount).Name
    		Next intCount
    
    	
    		' Dump the recordset to Excel
    		.Range("A2").CopyFromRecordset rst
     .Visible = True
    
      End With
    
    
     ' Add your error handler 
    End Function
    You could also use the Docmd.TransferSpreadsheet action, but this can be restrictive when you have dynamic queries to export.
    Regards
    Justin

  3. #3
    Join Date
    Oct 2002
    Posts
    88
    Bear with me, I don't know VBA much.

    When I try this code

    Dim rst ADODB.Recordset

    Is highlighted as an error of some sort. I have ADO 2.7 selected.

    I am also trying to put this in the on click event of a button and am not sure how to handle a function withing a sub.

    Thanks

  4. #4
    Join Date
    Mar 2005
    Posts
    93
    I had the same problem a while ago. What I did was using a temporary table to store the records (as results of the filters) in the subform. There is no Parent/Child link between the main form and the subform, but you will use a Requery statement. This way requires VB codes though.

  5. #5
    Join Date
    Oct 2002
    Posts
    88
    Can you give me some hints at how this is done.

    Do I abandon the other approach entirely and just save the query recordset as a tempory table and then use transfertext to put it in Excel?

    Thanks

  6. #6
    Join Date
    Nov 2004
    Posts
    33

    Here's another way

    You can use code to open the subform again. Just open the sub-form, without the parent form.

    Docmd.openform "SubForm"

    Include a where clause that will limit the records appropriately, and then print the sub-form. You will find that with the underlying recordset limited like this, you will only get the records selected.

    You will probably want to add a page header to the sub-form, which only appears when forms are printed out to paper. Use the header to include fields from your main parent form that you also need to include in the printout.

    I have used this technique when I was required to print out an actual form for whatever reason. Personally, I think you ought to create a report, if you have the choice, because printing a form always looks horrible and uses more paper than necessary. But I was stuck in that position and had to do it that way, and this is what I came up with.

  7. #7
    Join Date
    Oct 2002
    Posts
    88
    I don't want to print out the subform recordset. I would like to open it as an Excel file where it could be further manipulated via excel.

    There is an Analyze It With Excel option in the Tools/Office Links section but it only seems to work with main forms.

  8. #8
    Join Date
    Mar 2005
    Posts
    93
    Quote Originally Posted by tmort
    Can you give me some hints at how this is done.

    Do I abandon the other approach entirely and just save the query recordset as a tempory table and then use transfertext to put it in Excel?

    Thanks
    tmort,

    It's all depending on how you filter out your records in the subform. If you can have a snap shot of the criteria or your form, I can provide further information. Otherwise, you can use a parameter query instead of a temporary table, and the subform is connected to that query. Then use that query in the TransferSpreadsheet statement.

    Hope this helps!

  9. #9
    Join Date
    Nov 2010
    Posts
    1

    Wink This is how to do it

    I altered the code posted previously by someone else, but using DAO instead of ADO (because that's the data access syntax I know), and passing the parent form object into the function:

    Public Function ExportToExcel(frmParent as Form, frmSubForm as Form)
    Dim rs As Recordset
    Dim intCount As Integer
    ' Create the Excel object
    Set mobjXl = New Excel.Application

    ' Fetch the recordset
    Set rs = frmParent.Form.Controls("frmSubForm.name").Form.Re cordsetClone

    With mobjXl
    ' Add a workbook and turn of Excel updates
    .ScreenUpdating = True
    .visible = False
    .Workbooks.Add
    .DisplayAlerts = True

    ' Add the column headers
    For intCount = 0 To rs.Fields.Count - 1
    .Cells(1, intCount + 1).Value = rs.Fields(intCount).Name
    Next intCount


    ' Dump the recordset to Excel
    .Range("A2").CopyFromRecordset rs
    .visible = True

    End With


    ' Add your error handler
    End Function


    The export will reflect any filtering you had in place on the subform at the time you export it, but the field order will be dictated by that of the underlying query. Hope this helped.

    - Parker

  10. #10
    Join Date
    Aug 2012
    Posts
    2

    I had the similar problem

    Hi,
    I had the similar problem and managed to solve it with access add-in 'A2EE.mda'.
    It can export all subform's records related to current record of Main form.
    Unfortunatelly it is not free :-(
    L.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •