OutputTo using filtered datasheet outputs all records

[vmon]

Is there a way to output a filtered set of records to an Excel spreadsheet. I can send a filtered set of records to a report using this:

DoCmd.OpenReport stDocName, acPreview
With Reports(stDocName)
.OrderBy = strSort
.Filter = strWhere
.FilterOn = True
End With

Is there a way to do the same with the OutputTo? Here is the statement I am using:

DoCmd.OutputTo acOutputForm, "frmMpDispatchKoetterDataSheet_sub", acFormatXLS, , -1

Thanks,
vmon

 

[vmon]

I am taking this as "no can do" based on response . Is there maybe a different approach to what I am trying that might work

thanks,
vmon

 

[Dennisk]

you would apply a filter on the same line as the openreport statement.

 

[vmon]

you would apply a filter on the same line as the openreport statement.

This sounds like you suggest sending data to a report and then to Excel. I would like the data to go to Excel from the filtered datasheet but only the records being viewed in the filtered set. I have not been able to get a filter to work with the OutputTo. I know it works with the openreport.

 

[boblarson]

If it doesn't work with the filter, you may have to use the Excel Object Model to send it to Excel via code working with the Excel objects. That's the method I have normally used. It's definitely a bit more code than DoCmd.OutputTo, but it will work.

 

[vmon]

If it doesn't work with the filter, you may have to use the Excel Object Model to send it to Excel via code working with the Excel objects. That's the method I have normally used. It's definitely a bit more code than DoCmd.OutputTo, but it will work.

boblarson, Would you have a short example of the Excel Object Model that uses a filter you could share? Thanks, vmon

 

[llkhoutx]

Stumbled onto this thread today.

I think that you want to use

Docmd.Transferspreadsheet

to send Access data to Excel.

 

[ted.martin]

The way I use is run a MAKE TABLE query (either a a Query or SQL), which can be filtered and then Docmd.OutputTo acTable, etc. to Excel.