Exporting View with Filter

  • Thread starter Thread starter el zorro
  • Start date Start date
E

el zorro

I have a view that is filtered by input from the user:

stLinkCriteria = {user-specified month and year}
stDocName = "TestView"

DoCmd.OpenView stDocName, acViewNormal, acReadOnly
DoCmd.ApplyFilter , stLinkCriteria

THis seems to work, but I also want to help the user export the view to an
Excel file. I have tried this:

DoCmd.OutputTo acOutputTable, stDocName, "Microsoft Excel 5-7(*.xls)"

THis outputs the view as a table, but without the filter (i.e., it shows All
the months, not just those specified in stLinkCriteria. Is there a way to
export the filtered view? Or, more likey, a better way to do the whole
process? Thanks!
 
Not sure if the following pieces of code with work using the format
specification "Microsoft Excel 5-7(*.xls)" instead of acFormatXLS but you
can try them. Quoted from another post:

« You can use a Stored Procedure with the TransferSpreasheet command if you
are using an EXEC statement; for example:

DoCmd.OutputTo acOutputStoredProcedure, "Exec dbo.MySP parm1, ...",
acFormatXLS, "c:\test.xls"

For views, you must build a Select statement:

DoCmd.OutputTo acOutputStoredProcedure, "Select * from MyView where Id=1",
acFormatXLS, "c:\test2.xls"

Notice that you must use acOutputStoredProcedure or acOutputServerView in
both cases and that acOutputQuery shouldn't work. Don't forget to put dates
and string values between single quotes. »
 
Back
Top