Ouput report to excel file

[sabavno]

Hi

Is it possible to first apply the filter to the report and then output the report to the excel file?


With this code I just open the report applying the criteria:
DoCmd.OpenReport "WbtReport", acViewPreview, , "[AssetStatus]='active'"

Can I actually send that filtered report to excel not even opening the report?

Thanks

 

[mincefish]

You'd need to kinda alter the way you run this, but one of the ways I can output to Excel, or Open the report, is to use the same the same query for a report, and simply change the .SQL property of the query. Here's how (to start off with!) - this code assumes that you are taking the criteria from a combo box (for example you might have a combo box for
AssetStatus that has two values - Inactive, or Active...

Function fncChangeQuery()
Dim qdfTemp as querydef
Dim strSQL as string

strSQL = "SELECT a,b,c FROM tbl WHERE AssetStatus = '" & me.cboAssetStatus.Value & "'" 

Set qdfTemp = CurrentDb.QueryDefs("qryWbtReport")

What I've done there is to open up the queries collection, specifically the query that runs the report

qdfTemp.SQL = strSQL
Set qdfTemp = Nothing

I then assign strSQL to the SQL property of the Query. Finally set qdfTemp to nothing so I don't have open objects.

End Function

From here you can call a function to either output the report to Excel (DoCmd.OutputTo), or run the report, depending on what the user has requested!

HTH

Mincefish