export form to Excel limiting records

[Guest]

I have a form that allows the users to select criteria that is then displayed
in a subform. The user needs to be able to export the filtered (requeried)
resultset to EXCEL.
I have used the following code, but it exports all the records instead of
just a few. What is the correct way to do this?

'export the results to Excel
DoCmd.OutputTo acOutputForm, "subfrmData", acFormatXLS, "1.xls", True

 

[Shaun]

This is exactly my problem as well. I'm trying to set the
ObjectName string to a query or the recordset of the
subform and only get nothing or everything.

I've also tried using the DoCmd.TransferSpreadsheet and
the Application.ExportXML with the same results.

Any help would be greatly appreciated.

thanks,
Shaun

 

[Ken Snell [MVP]]

Use TransferSpreadsheet. It doesn't export as a formatted output, which is
what OutputTo is doing.

Also, OutputTo will truncate the export at 16,384 records, and will truncate
all text strings longer than 255 characters.

 

[Ken Snell [MVP]]

Sorry... wrong answer for your question.... read too quickly.

TransferSpreadsheet and OutputTo will export data from a table or a query.
Neither will export a filtered recordset such as you have in your form's
recordsource.

You would need to create a temporary table or query that has the desired
data, export that table or query, and then delete the table or query after
the export.

Might be easier to do a query for your setup. Sample code (assumes that
you're running the code from the main form, and you want to export the data
from the subform in that form):

Dim dbs As DAO.Database
Dim qdf As DAO.QueryDef
Dim strSQL As String
Set dbs = CurrentDb
strSQL = Me.SubformName.Form.RecordSource
Set qdf = dbs.CreateQueryDefs("MyTempQueryName", strSQL)
qdf.Close
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
qdf.Name, "C:\MyFolder\FileName.xls"
DoCmd.DeleteObject acQuery, qdf.Name
Set qdf = Nothing
dbs.Close
Set dbs = Nothing

--

Ken Snell
<MS ACCESS MVP>