Can't export data in a filtered subform only

**mayestom** · 08-10-2014, 12:09 PM

However, compiling after the first code snippet I provided will catch if you got the Subform Control Name correct or not.

Okay understood. Everything compiled.

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.

As soon as I deleted the name of my control a compile error message popped up. I put back the name of my control.

So I added the second bit of code to the Event Procedure. Went to form view to test. It worked!!!! YEAH, really happy, thank you!!! I pushed the button and it opened an Excel file with only the filtered records from the subform. But...
When I went back to Access and pushed the button again (with all the same records still showing) a messaged popped up stating "No records found". So I closed the form and opened it again to the selected records and the button worked. Closed the Excel file and pushed the button again and same messaged popped up again.
Also tried keeping the form open and selected different records to show and pushed the button. Same message showed that no records found. If I closed the form and reopened it with different records and pushed button, it worked.
Can the button only be used once when the form is open?

Can this code be used on other forms/subforms/queries? I would obviously need to change control names. For example I have a form with combo boxes at the top of a form that are used as filters. The bottom of the form displays the results of the query. Can I use this code to apply a export button on this form?

**ItsMe** · 08-10-2014, 12:40 PM

The following code is to try to avoid Run Time Errors.

Code:

 If rsClone.EOF Then
MsgBox "No records found."
 Set rsClone = Nothing
 Exit Sub
 End If

If you just wanted to create more excel files, you can do that without having to, repeatedly, click the button.
If you anticipate using the button more than once before moving to a new record, you could add some additional code.

Code:

If rsClone.RecordCount > 0 Then
rsClone.MoveFirst
End If

It is also good practice to destroy what you create and close what you open. You do this to prevent memory leaks as well as ensuring that your are not using old data/objects.

Code:

If Me.Dirty Then Me.Dirty = False
Dim rsClone As DAO.Recordset
 Set rsClone = Me.ctrForExport.Form.RecordsetClone

If rsClone.RecordCount > 0 Then
rsClone.MoveFirst
End If
 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
'tidy up
rsClone.Close
Set rsClone = Nothing
'It would be proper to save and close the spreadsheet.
'For demonstration purposes we will leave the .Visible = true
'and the file open
Set xlApp = Nothing

**mayestom** · 08-10-2014, 01:31 PM

I added:

Code:

If rsClone.RecordCount > 0 Then
rsClone.MoveFirst
End If

That did it.

This is what I have so far:

Code:

Private Sub cmdExportToExcel_Click()
If Me.Dirty Then Me.Dirty = False
 Dim rsClone As DAO.Recordset
  Set rsClone = Me.[Packaging information charts - Copy Of subform].Form.RecordsetClone
 
 If rsClone.RecordCount > 0 Then
 rsClone.MoveFirst
 End If
 
  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

What do you mean by this:

It is also good practice to destroy what you create and close what you open. You do this to prevent memory leaks as well as ensuring that your are not using old data/objects.

Should I add the last bit of code that you have? What does it do?

Code:

'tidy up
rsClone.Close
Set rsClone = Nothing
'It would be proper to save and close the spreadsheet.
'For demonstration purposes we will leave the .Visible = true
'and the file open
Set xlApp = Nothing

**ItsMe** · 08-10-2014, 01:59 PM

Should I add the last bit of code that you have? What does it do?

After you instantiate an object, it is a good idea to Set it to = Nothing. Likewise, if you use the Open method, you should close it.

You would do this sort of thing after you are done with the Objects you created. If you are not interested in saving your Excel file to a folder and or doing additional formatting, etc. Then you should be OK with that small amount of code I provided at the end. However, to avoid memory leaks, it would be best to close down the excel instance you opened.

Code:

'tidy up
rsClone.Close
Set rsClone = Nothing
'Before closing, we need to add some code to save the file we created
xlApp.ActiveWorkbook.SaveAs ("C:\Test\MyExport.xlsx")
'Now we can close
xlApp.ActiveWorkbook.Close
xlApp.Quit
Set xlApp = Nothing

**mayestom** · 08-10-2014, 02:30 PM

I opted for:

Code:

'tidy up
rsClone.Close
Set rsClone = Nothing
'It would be proper to save and close the spreadsheet.
'For demonstration purposes we will leave the .Visible = true
'and the file open
Set xlApp = Nothing

bc I'm not the only one using this db so I don't know where others are going to want to save the Excel file.
Although should I still add:

Code:

xlApp.ActiveWorkbook.Close
xlApp.Quit
Set xlApp = Nothing

to the end?

Also, I would like to put your name in the code notes. What should I add and where?

**ItsMe** · 08-10-2014, 04:13 PM

If you do not close the Excel Object (xlApp.ActiveWorkbook.Close) there will be a memory leak. The object will remain in memory until the user logs off of Windows. If you do use xlApp.ActiveWorkbook.Close, the user may not understand the prompt to save the file.

Another option would be... You can have them choose a folder using the File Picker. You would need to do a couple of things. First, you need to reference a library. To do this you need to go to Tools in your VBA editor's Menu Bar (top of the window) and select References. When the references window opens up you need to select and tick the box next to
Microsoft Office XX.0 Object Library

Where XX.0 represents your version of access. Referencing version specific libraries can be tricky but this one is forgiving in that, it will work across different versions of office.

You will want to change your original code. Change .Visible = True to...
.Visible = False

And then you will want to add some code. Here is a tested version.

Code:

If Me.Dirty Then Me.Dirty = False
Dim rsClone As DAO.Recordset
Set rsClone = Me.ctrForExport.Form.RecordsetClone
    If rsClone.RecordCount > 0 Then
        rsClone.MoveFirst
    End If
    
        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 = False
        .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
Dim strpath As String
    With Application.FileDialog(msoFileDialogFolderPicker)
    
        .Title = "Locate a folder to export"
        .ButtonName = "Choose"
'        .InitialFileName = "C:\"
        .InitialView = msoFileDialogViewThumbnail
        
            If .Show = 0 Then
                Exit Sub
            End If
            
        strpath = Trim(.SelectedItems(1))
        
    End With
strpath = strpath & "\MyExport_" & Format(Now, "mm_dd_yy_hh_mm_ss") & ".xlsx"
'tidy up
rsClone.Close
Set rsClone = Nothing
'Before closing, we need to add some code to save the file we created
xlApp.ActiveWorkbook.SaveAs (strpath)
'Now we can close
xlApp.ActiveWorkbook.Close
xlApp.Quit
Set xlApp = Nothing

**mayestom** · 08-10-2014, 07:01 PM

My goodness I can't believe this has gotten so complicated!

I would prefer if the data opened up in the Excel file and gave the user the option to save the file if they choose to. Can I set it up that way? Or will that leave the door open to memory leaks?
The way it is now the user only has the option of where the file is being saved. He/she dosn't know what data is being saved and can't name the file.

**ItsMe** · 08-10-2014, 08:11 PM

You can use this as your code to close things down. Give it a try. The user will get a prompt to save the file. It may be confusing to the User so maybe you can walk them through the process a couple of times.

Code:

'tidy up
rsClone.Close
Set rsClone = Nothing
xlApp.ActiveWorkbook.Close
xlApp.Quit
Set xlApp = Nothing
Set xlApp = Nothing

I hope you are not put off by all of the code I threw your way. Developing apps has never been simple. It has always been complicated. If you can easily and quickly throw an app together, chances are it will not be very stable or, if it is stable, it may not have longevity.

Get your app up and running. After a few days or a week or so, review this thread again. You may see one or two things in a new light, after your brain gets some rest.

**mayestom** · 08-11-2014, 06:02 AM

This still only opens up the folder picker. Can't I change it back to having it open Excel to see the data and give the user the option of what they want to do?

**ItsMe** · 08-11-2014, 07:24 AM

The appropriate way would be to use the File Picker and close your Objects. Then, use a different process to open the saved file so the User can view and edit at their will. A quick and dirty way would be the original Tidy Up code.

Code:

'tidy up
rsClone.Close
Set rsClone = Nothing
'It would be proper to save and close the spreadsheet.
'For demonstration purposes we will leave the .Visible = true
'and the file open
Set xlApp = Nothing

**mayestom** · 08-11-2014, 07:44 AM

This is what I have presently. How does it look to you?:

Code:

Private Sub cmdExportToExcel_Click()
If Me.Dirty Then Me.Dirty = False
Dim rsClone As DAO.Recordset
Set rsClone = Me.[Packaging information charts - Copy Of subform].Form.RecordsetClone

If rsClone.RecordCount > 0 Then
rsClone.MoveFirst
End If

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

'tidy up
rsClone.Close
Set rsClone = Nothing
xlApp.ActiveWorkbook.Close
xlApp.Quit
Set xlApp = Nothing
Set xlApp = Nothing
'A big Thank You to ItsMe for all the help, guidance, patience and understanding
End Sub

**ItsMe** · 08-11-2014, 08:06 AM

That looks good to me. With this example, the file will close automatically. Because you have .Visible = True, the user will be able to see the question generated by Excel, asking if they want to save changes. Afterwards, the user will need to find their file and open it if they want to do further edits and or view it.

Just be sure to walk your users through the process. A big struggle with developing apps is making the app intuitive for the user. Sometimes it is easier to have the app train the user, sometimes it is easier for you or a power user train the Users.

You went through a lot of things for this small task. However, you were introduced to many nuances that many people choose to ignore as they create applications. Now, you are less likely to kludge code you find on the internet into your app and more likely to analyze the code first. In the future, use your knowledge to ask questions and gain more knowledge.

**mayestom** · 08-11-2014, 08:28 AM

Thank you ItsMe!!! There is no way I would have been able to figure this out by myself. I very much appreciate your time and effort in helping me with this. I have more knowledge because of you and have an appetite to learn more about how to write code.
In your opinion, what class or classes would I look for at a community college to learn this?

**ItsMe** · 08-11-2014, 08:48 AM

Computer Science. It is a long road. There are so many different things/topics about computers now that an entire carreer could, possibly, be dedicated to computers but not ever touch on 95% of the topics.

Here is a list of topics that are specific to Microsoft. The link is just an idea of some topics. Some tutorials are good for learning while others are not as good.
http://www.microsoftvirtualacademy.com/

Access is an RDBMS and this is specific to Data Management/Data Administration. Access is also a tool that can be used to create Desktop Applications. In addition to being an RDBMS, Access is a Rapid Application Development tool (RAD). This thread has been specific to the RAD side of Access. Here is some info that is crucial to knowing Access. It is a place to start. Browse the various links within. In order to be able to build an application (especially Business Applications) you need to know RDBMS.
https://www.accessforums.net/tutoria...ase-45451.html

If you really want to be a developer or at least be serious about it...
http://www.learnvisualstudio.net/

**mayestom** · 08-11-2014, 09:00 AM

Thank you for the links ItsMe. I will start to explore them in the coming week/weeks.
And again, thank you for all your help.

Can't export data in a filtered subform only

Thread Tools

Similar Threads

Export Filtered Query to Excel Using Combo Box on Form

Export Filtered ListBox output to a Excel Sheet

Export Filtered Results to Excel Tabs

DCount on filtered subform

Displaying filtered data in subform

Posting Permissions