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.
DoCmd.OpenReport strReportName, acViewPreview, , , , strLongOpenArgs
DoCmd.OutputTo acOutputReport, strReportName, acFormatXLS, Me.txtReportFileName
DoCmd.Close acReport, strReportName

Open in new window

Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dale FyeOwner, Developing Solutions LLCCommented:
This method works just fine for me, with or without the filename, but I did it in the immediate window.

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.
colinasadAuthor Commented:
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).
Dale FyeOwner, Developing Solutions LLCCommented:
I understand the use of the OpenArgs parameter to pass filters and OrderBy clauses to the report, although I generally use the WhereCondition of the OpenReport method to pass the filter, and the reports Grouping and Sorting parameters will override the OrderBy clause.

When you inserted the breakpoint, it still gave you the error message?

How many pages is this report?

Try this.  Copy the OutputTo line and paste it in the immediate window.  Replace strReportName with the actual name of the report, and drop the filename, then hit enter.  Does Access ask you for a file name? Do you get the same error as previously.  I know this report won't be filtered, but I'm trying to isolate the problem.

DoCmd.OutputTo acOutputReport, "ReportName", acFormatXLS

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
colinasadAuthor Commented:
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.
Dale FyeOwner, Developing Solutions LLCCommented:
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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.