Extract to Excel

[101287]

I have a form that I can filter the records by site. I would like to use this filter form and add a buttom that will output the filter records to excel.
Is there any guidance on how to do that? Or any website that can show code to perform this.

Tried to use a macro but the macro does not provide this capability.

Your guidance to accomplish this will be appreciated.

Thank you - Luis

 

[PHV]

Have a look at the DoCmd.TransferSpreadsheet method.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[101287]

Review the DoCmd.TransferSpreadsheet but it does not give me the flexibility to transfer a dynamic query or filter to an excel spreadsheet.

Other suggestions, please.

 

[PHV]

it does not give me the flexibility to transfer a dynamic query
Really ?
Have a look at the QueryDefs collection.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[101287]

Your are correct. It works.

The code is below.
Public Sub OTXLOut(sql as String)
CurrentDb.QueryDefs("qryBasic").sql = sql
DoCmd.OutputTo acOutputQuery, "qryBasic", acFormatXLS, "C:\Test.xls"
End Sub

Thank you. Great !

 

[CautionMP]

101287,
[ol][li]Use the form field as the filter for the query behind for form, this will allow you to use [tt]TransferSpreadsheet[/tt]/[tt]OutputTo[/tt].[/li]
[li]Use a UDF to expot to Excel using an SQL string as the record source (Export A Dynamic Query TO Excel).[/li][/ol]

Hope this helps,
CMP

[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT-07:00) Mountain Time (US & Canada)