docmd.Transferspreadsheet acExport

[Leinad]

I am trying to create a button on a form that has many filters on it that
filters the underlying subform. I want to be able to transfer the data to
excel from the filtered subform on the click of this button and I only want
the filtered data to be transfered. I attached the code at the bottom. I
would greatly appreciate if someone can help me out.


Private Sub Command16_Click()
Dim app As Excel.Application
Dim xl As Excel.Workbook

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "tbl_Tickets",
"C:\MyPath", True

Set app = CreateObject("Excel.Application")
Set xl = app.Workbooks.Open("C:\MyPath")
app.Visible = True
Set ws = xl.Worksheets(1)
Set ws = Nothing
Set xl = Nothing
End Sub

 

[Allen Browne]

Create a query to use for the export. Before you TransferSpreadsheet, modify
the WHERE clause so the filter works as desired.

This kind of thing:

Private Sub Command16_Click()
Dim strWhere As String
If Me.FilterOn Then
strWhere = " WHERE " & Me.Filter
End If
Currentdb.QueryDefs("qryExport").SQL = "SELECT tbl_Tickets.* FROM
tbl_Tickets " & strWhere
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
"qryExport", ...

If you are using Access 2002 or later, the form's filter may refer to things
other than fields in the table (typically where you have a combo where the
bound field is not the display column.) You would need to modify things if
you hit that problem.

 

[Ken Snell \(MVP\)]

You will need to create and save a query that represents the filtered
version of the subform's RecordSource query.

Although not exactly what you seek, take a look at these articles to see how
to create and save a query and then use it to export to EXCEL:

Create and Export a Parameter Query to EXCEL file via TransferSpreadsheet
http://www.accessmvp.com/KDSnell/EXCEL_ImpExp.htm#CreateExportParam


Create a Query and Export multiple "filtered" versions of a Query (based on
data in another table) to separate EXCEL files via TransferSpreadsheet
http://www.accessmvp.com/KDSnell/EXCEL_ImpExp.htm#FilterExportSepFiles


Create a Query and Export multiple "filtered" versions of a Query (based on
data in another table) to separate Worksheets within one EXCEL file via
TransferSpreadsheet
http://www.accessmvp.com/KDSnell/EXCEL_ImpExp.htm#FilterExportSameFile