Why does my attempt to "DoCmd.OutputTo acOutputReport, strReportName, acFormatXLS" fail?
I'm developing an Access 2007 "project" (.ADP) as a front-end to a SQL Server 2005 Express database.
I would like to give the user the option of saving a report as an Excel spreadsheet, but the attached code always generates the error message :
Error Numb : 2587 " .. can't complete the Ouput operation"
If I use "acFormatRTF" instead of "acFormatXLS" it works OK and creates a text file.
Also, "acFormatXLS" works OK if I try to export the contents of a Table using "acOutputTable".
Can anyone tell me why the "acOutputReport / acFormatXLS" combination does not work?
I have also tried to use "acOutputQuery" and supplied the name of a SQL Server "view", but that creates a similar error message.
I would like to give the user the option of saving a report as an Excel spreadsheet, but the attached code always generates the error message :
Error Numb : 2587 " .. can't complete the Ouput operation"
If I use "acFormatRTF" instead of "acFormatXLS" it works OK and creates a text file.
Also, "acFormatXLS" works OK if I try to export the contents of a Table using "acOutputTable".
Can anyone tell me why the "acOutputReport / acFormatXLS" combination does not work?
I have also tried to use "acOutputQuery" and supplied the name of a SQL Server "view", but that creates a similar error message.
DoCmd.OpenReport strReportName, acViewPreview, , , , strLongOpenArgs
DoCmd.OutputTo acOutputReport, strReportName, acFormatXLS, Me.txtReportFileName
DoCmd.Close acReport, strReportName
ASKER
Thanks for the prompt response, fved.
The "strLongOpenArgs" is an "Open Arguments" parameter containing a "TopNNN" clause, a "WHERE" clause and an "ORDER BY" clause, concatenated together into a single string. The "Report_Open" procedure adjusts its "RecordSource" property with these values. This is necessary because the operator makes various choices and selections before starting the report. It is a fairly well tried and tested strategy that doesn't cause problems anywhere else.
I inserted a breakpoint as you suggested and the OutputTo variable names were all OK. (I use exactly the same code for the "acFormatRTF" output that works OK).
I actually do have a "Message Box" command between my "DoCmd.OpenReport" and "DoCmd.OutputTo" lines where I ask the operator to confirm that they want to save the Report while its "preview" is visible in the background, so the report is pretty well established by that time.
I have also experimented with a "DoEvents" command before the "OutputTo" instruction, but that hasn't had any effect.
PS I discovered there is an "acOutputServerView" option that does allow me to output a SQL Server "view" as "acFormatXLS" OK (except larger views don't get saved because I'm exceeding a rows limit somewhere).
The "strLongOpenArgs" is an "Open Arguments" parameter containing a "TopNNN" clause, a "WHERE" clause and an "ORDER BY" clause, concatenated together into a single string. The "Report_Open" procedure adjusts its "RecordSource" property with these values. This is necessary because the operator makes various choices and selections before starting the report. It is a fairly well tried and tested strategy that doesn't cause problems anywhere else.
I inserted a breakpoint as you suggested and the OutputTo variable names were all OK. (I use exactly the same code for the "acFormatRTF" output that works OK).
I actually do have a "Message Box" command between my "DoCmd.OpenReport" and "DoCmd.OutputTo" lines where I ask the operator to confirm that they want to save the Report while its "preview" is visible in the background, so the report is pretty well established by that time.
I have also experimented with a "DoEvents" command before the "OutputTo" instruction, but that hasn't had any effect.
PS I discovered there is an "acOutputServerView" option that does allow me to output a SQL Server "view" as "acFormatXLS" OK (except larger views don't get saved because I'm exceeding a rows limit somewhere).
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Sorry I've been away from this for a couple of weeks.
I tried the "Immediate Window" strategy as suggested and still get the same error message.
The report is created and appears in its own preview Window on the screen.
Without a specified destination file, the regular Windows "Output to" dialog appears asking where to save the file.
(Offering an xls file type) After the "OK" button is clicked, a brief "Now outputting" message is flashed on the screen, then the "can't complete the Output operation" error message appears.
I would like to close this question but was unsure how to award points - which I'm quite happy to do - and how to rate the response. fyed says his solution works for him but it doesn't work for me. I am therefore happy to add the question to the knowledge-base in the hope that it works for others.
Many thanks for your patience and help.
I tried the "Immediate Window" strategy as suggested and still get the same error message.
The report is created and appears in its own preview Window on the screen.
Without a specified destination file, the regular Windows "Output to" dialog appears asking where to save the file.
(Offering an xls file type) After the "OK" button is clicked, a brief "Now outputting" message is flashed on the screen, then the "can't complete the Output operation" error message appears.
I would like to close this question but was unsure how to award points - which I'm quite happy to do - and how to rate the response. fyed says his solution works for him but it doesn't work for me. I am therefore happy to add the question to the knowledge-base in the hope that it works for others.
Many thanks for your patience and help.
Really would like to try to help you resolve this. Any chance you can post your database, or the applicable portions to this site for me to look at? If not, how about providing a little broader look at the code, other than just the three lines you posted? For example, how do you get the value for Me.txtReportFileName?
How many rows/columns in the query that this report is based upon? It could be that you are exceeding the limits of Excel, in which case you might need to break the report into pages and get them into separate pages in the Excel spreadsheet.
How many rows/columns in the query that this report is based upon? It could be that you are exceeding the limits of Excel, in which case you might need to break the report into pages and get them into separate pages in the Excel spreadsheet.
Have you put a breakpoint on that line to determine what the value of me.txtReportFileName is at the time? If you do this, when the code breaks, hover over the filename and see what it reads, then press F8 or F5. If the filename seems valid, and the code runs OK, my guess is that you will probably just need to put a pause between the OpenReport and the OutputTo lines. It may be that the report is not fully open and loaded when the code tries to execute the OutputTo method
You might want to try eliminating the OpenReport method altogether. This may not be feasible since it appears you are passing one or more arguments to the report, which I assume you are using in some code within the report.