Report export issue

[Irina]

I have a report that I can export to excel with no problems MANUALLY,
but when I create a command button for a user to click on and export
the same report to excel using VB Code,
I get an error message: Error 9 Subscript out
of range.
the code for the button is this:
Dim stDocName As String
stDocName = "rptClientReport"
DoCmd.OutputTo acOutputReport, stDocName, acFormatXLS,
"C:\Reports\ClientReport.xls"
Can anyone advise why this code does not work?
And yet if I go under file->export->
I can export the very same report with no issues?

please help

 

[George Nicholson]

Seems like that should work.

1) Question: Does C:\Reports already exist? I don't think OutputTo will
create a directory, and "subscript out of range.." and "directory not found"
could be interpreted as the same message if you squint real hard...

2) One thing you could try (for testing) is to shorten the command to:
DoCmd.OutputTo acOutputReport, stDocName
Help says that if you omit the last 2 arguments, you will be prompted for
values. You might try it and see if the error still pops. In any case it
might help you zero in on the problematic argument (assuming there's only
one).

 

[Jeanette Cunningham]

Irina,
I am interested to see if you tried it with Transfer Spreadsheet in place of
OutputTo?
Did you try it and did it work? That could give a clue to the problem you
are having.


Jeanette Cunningham -- Melbourne Victoria Australia