Page 1 of 3 123 LastLast
Results 1 to 15 of 31
  1. #1
    mayestom is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2014
    Posts
    31

    Can't export data in a filtered subform only

    I have a subform in datasheet view with filtered data in it. I want to be able to export that filtered data to an Excel file. I know there is a macro that does this (exportwithformatting) but when I create the macro and place it on my form it exports all the data in the parent table not the data that’s showing in the subform. Can someone explain how to export to an Excel file just the data that's showing in the subform only?

  2. #2
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,862
    Use DAO to create a RecordsetClone of your subform.

    Me.SubformControl.Form.RecordsetClone

  3. #3
    mayestom is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2014
    Posts
    31
    Thank you for your reply ItsMe.
    I'm sorry I don't know what this means? I'm relatively new so you need to sort of spell things out for me.
    Use DAO to create a RecordsetClone of your subform.

    Me.SubformControl.Form.RecordsetClone

  4. #4
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,862
    Recordset clone of a subform will look something like this

    if me.dirty then me.dirty = false
    Dim rsClone as DAO.recordset
    set rsClone = me.SubformControl.form.recordsetclone

    if rsclone.eof then
    msgbox "No records found."
    set rsclone = nothing
    exit sub
    end if

    With that, you could then employ some code to export the recordset. Maybe something like CopyFromRecordset
    https://www.accessforums.net/program...tml#post210391

  5. #5
    mayestom is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2014
    Posts
    31
    ItsMe I really appreciate your help.
    But I think you are talking about code which I’m not versed in. I'm sorry, I don’t know what a redcordset clone is? Where do I find it? I’m looking in the property sheet of the subform but the closest I can find is Recordset Type set to "Dynaset".
    As mentioned before I'm new and don't know my way around to well yet. Would you be able to explain what it is I'm to do?
    Thank you.

  6. #6
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,862
    What I am talking about is using VBA. The only, "feasible", way I know to export filtered data from a form or subform is to create a Recordset Clone of the form's recordset and export the clone. To do this you need to write some VBA code. Withinn the VBA, you will need to include some code that employs the DAO library. There is a VBA example of creating a recordset clone in post #4.

    Perhaps you should familiarize yourself with VBA and how to use a control's and or form's members to handle events. A starting point might be to practice with a new blank form and a single Command Button Control. Behind the Command Button's On Click event you could place some VBA. Some sample VBA might be...

    MsgBox "Hello World"

  7. #7
    mayestom is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2014
    Posts
    31
    Okay, I understand. Now I just need to figure out how to write the code in order to be able to do this.
    Thank you for your time ItsMe.

  8. #8
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,862
    Try something. Afterwards, let us know what you tried and what the results of your efforts are.

  9. #9
    mayestom is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2014
    Posts
    31
    What I've tried so far:
    Macro with exportwithformatting of the form and of the subform
    Macro with printpreview or printobject or printout
    created a report
    tried creating a query of the subform
    tried a couple other things but can't remember them now.


    Oh, one thing that did work; right-clicked on the top left corner of subform copied and pasted into an Excel file. Not to convenient. But it worked
    I'll keep looking and I'll let you know what I find,
    Thank you.

  10. #10
    eki einstein's Avatar
    eki einstein is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Jan 2013
    Location
    Bekasi - Jawa Barat- Indonesia
    Posts
    26
    i have some problem just like you have, but it solve if you dont use the advance filter in ms acces, build a Query and filter box in form and describe the query criteria as the filterbox in subform that you used, and create a macro that export to excell based on your Query not subform than it will work....

  11. #11
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,862
    mayestom,

    It sounds as though you have tried everything except writing some VBA. As I mentioned before, the only, "feasible", way I know to export filtered data from a form or subform is to create a Recordset Clone.

    Let me know if you are interested in creating some VBA to get the job done.

  12. #12
    mayestom is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2014
    Posts
    31
    ItsMe - Yes, thank you I would very much like that, but as I've mentioned before I don't know how to write code. I've taken a Java class and some of the sql code looks sorta kinda familiar. But I just don't know where to start. I did try the message box like you suggested and that worked (very cool, thank you). And I've been reading up on the code writing section in my self-help book for Access, but other then that, I'm at a loss as to where to begin/what to do.

    eki einstein - thank you for your reply. I will keep it in mind if I can't write code for this, because it sounds like that is the proper way to accomplish this and to get me started in writing code. But thank you for your input.

  13. #13
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,862
    I suggest you create a copy of your form that has the subform, to practice. You can create a Recordset Clone with the following... You will need to replace SubformControl with the name of your subform control.
    Code:
    if me.dirty then me.dirty = false
     Dim rsClone as DAO.recordset
     set rsClone = me.SubformControl.form.recordsetclone
    
     if rsclone.eof then
     msgbox "No records found."
     set rsclone = nothing
     exit sub
     end if
    Place the above code snippet in the Click event procedure of a Command Button Control. Your button should be part of the Main form and not part of the subform. As you add/type code to your main form's module, you will want to Compile your code. Use Debug>Compile Database from the Menu Bar within the VBA editor.

    If everything compiles OK, youi will want to add some more code. You can double check that your rsClone object that you instantiated did, indeed, instantiate by typing rsClone and dot (rsClone.) to get intellisense to appear.

    Once you have your recordset object good to go, you can add additional code to create an object to Automate Excel. The additional code will look something like this.

    Code:
        Dim xlApp As Object
        Set xlApp = CreateObject("Excel.Application")
        With xlApp
            .Visible = True
            .Workbooks.Add
            .Sheets("Sheet1").Select
            
            .ActiveSheet.Range("A2").CopyFromRecordset rsClone
            
            For i = 1 To rsClone.Fields.Count
                xlApp.ActiveSheet.Cells(1, i).Value = rsClone.Fields(i - 1).Name
            Next i
            xlApp.Cells.EntireColumn.AutoFit
        End With
    This is a very simple and straightforward way to export data to an Excel spreadsheet. If you want to do additional stuff, like save the file, add additional formatting, etc., you will need additional code. This example uses Late Binding. If you want to do a lot of Automation of your Excel object you may want to consider Early Binding. Early Binding requires a reference to a library but, it will provide Intellisense for your instantiated objects.


    I tested the following in a new blank DB. The subform control's name was ctrForExport. The code is a complete Sub Procedure for a Click event for a Command Button named Command0

    Code:
    Private Sub Command0_Click()
    
    If Me.Dirty Then Me.Dirty = False
    
    Dim rsClone As DAO.Recordset
    Set rsClone = Me.ctrForExport.Form.RecordsetClone
    
    If rsClone.EOF Then
    MsgBox "No records found."
    Set rsClone = Nothing
    Exit Sub
    End If
    
        Dim xlApp As Object
        Set xlApp = CreateObject("Excel.Application")
        With xlApp
            .Visible = True
            .Workbooks.Add
            .Sheets("Sheet1").Select
            
            .ActiveSheet.Range("A2").CopyFromRecordset rsClone
            
            For i = 1 To rsClone.Fields.Count
                xlApp.ActiveSheet.Cells(1, i).Value = rsClone.Fields(i - 1).Name
            Next i
            xlApp.Cells.EntireColumn.AutoFit
        End With
        
        
    End Sub

  14. #14
    mayestom is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2014
    Posts
    31
    Wow! Okay this is great! Thank you ItsMe.
    Sorry it has taken me so long to start work on this.


    Questions:
    -The name of the subform control is the Source Object name, correct?
    -So when I compile that gets rid of unused code/makes sure I type things in, is that correct?


    -I don't understand how to check?
    You can double check that your rsClone object that you instantiated did, indeed, instantiate by typing rsClone and dot (rsClone.) to get intellisense to appear.
    -Once I am able to double check:
    Once you have your recordset object good to go, you can add additional code to create an object to Automate Excel.
    When you say create an object to Automate Excel, what do you mean by that? Do you mean the command button that I placed the Recordset clone behind? Or create another command button on the form?

  15. #15
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,862
    -The name of the subform control is the Source Object name, correct?
    No, you can find The Name property for controls in the Property Sheet, under the Other tab. Highlight a control to look at the property sheet.

    So when I compile that gets rid of unused code/makes sure I type things in, is that correct?
    No, It will not get rid if the VBA you see. Compiling takes what you wrote in VBA and changes it/translates it to a different language, behind the scenes. It is compiled into a language that can be understood by the Run Time. Periodically compiling your code will check if the VBA you wrote can be translated. Compiled VBA does not guarantee your code will not experience a Run Time error. However, compiling after the first code snippet I provided will catch if you got the Subform Control Name correct or not.


    -I don't understand how to check?
    With the VBA editor open, go to the toolbar menu at the top of the Window, where File - etc. is. Click Debug and select "Compile Database". Afterwards, somewhere below "set rsClone = me.SubformControl.form.recordsetclone", you can type "rsClone."

    When you type the dot, intellisense should show up. Then, erase your rsClone. It was just temporary to understand what intellisense is.

    When you say create an object to Automate Excel, what do you mean by that?
    You will not need anything more than the one Command Button control to test your code and add the objects I am referring to. Objects can be instantiated using VBA. The creation of the objects is something that is created within memory. They are referred to as objects when these instances in memory depend on a Library. In this case, you are adding VBA to create an object that depends on the Excel Object Library (or some words close to that description).

    Simply, add the code to your Click Event Procedure as I described. I described adding it in two stages. The first stage is for your Recordset Clone object and the second stage is for your Excel object. There is a complete example in the third VBA code snippet.

Page 1 of 3 123 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Export Filtered Query to Excel Using Combo Box on Form
    By besuchanko in forum Import/Export Data
    Replies: 0
    Last Post: 02-07-2013, 10:10 PM
  2. Export Filtered ListBox output to a Excel Sheet
    By gokul1242 in forum Programming
    Replies: 4
    Last Post: 10-11-2012, 02:19 PM
  3. Export Filtered Results to Excel Tabs
    By opod in forum Programming
    Replies: 1
    Last Post: 08-03-2011, 01:33 PM
  4. DCount on filtered subform
    By TheShabz in forum Forms
    Replies: 2
    Last Post: 07-13-2011, 02:22 PM
  5. Displaying filtered data in subform
    By crxftw in forum Forms
    Replies: 11
    Last Post: 06-17-2011, 09:59 AM

Posting Permissions

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