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?
You could use code such as this (NOTE: you need a referece to ADO and Excel under VBA, References):
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
.ActiveConnection = CurrentProject.Connection.ConnectionString
.CursorLocation = adUseClient
.CursorType = adOpenForwardOnly
.LockType = adLockReadOnly
.MaxRecords = 65000 ' Approx max number of rows Excel can handle
' Add a workbook and turn of Excel updates
.ScreenUpdating = False
.Visible = False
.DisplayAlerts = False
' Add the column headers
For intCount = 0 To rst.Fields.COUNT - 1
.Cells(1, intCount + 1).Value = rst.Fields(intCount).Name
' Dump the recordset to Excel
.Visible = True
' Add your error handler
You could also use the Docmd.TransferSpreadsheet action, but this can be restrictive when you have dynamic queries to export.
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.
You can use code to open the subform again. Just open the sub-form, without the parent form.
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.
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?
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.