Export report to excel ERROR - please help

[Irina]

I have a report that I can export to excel with no problems,
when I create a command button for a user to click on and export
the same report to excel 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, "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

 

[Jeanette Cunningham]

Irina,
OutputTo is expecting the path to the file you are exporting to as well as
its name.
For example
--> "C:\Temp\ClientReport.xls" instead of just "ClientReport.xls"


Jeanette Cunningham -- Melbourne Victoria Australia

 

[Irina]

Hi Jeanette,
I created a folder called reports on my C drive.
and changed the path to C:\reports\ClientReport.xls in my code.
However, I am still getting the error message: "RunError 9.
Subscript out of range."
Do you or anyone else have any other ideas? I really need to make this work
thanks

 

[Jeanette Cunningham]

Irina,
use TransferSpreadsheet instead of OutputTo.
The syntax is similar, but not quite the same.
Use vba help on TransferSpreadsheet to get the arguments correct.
TransferSpreadsheet is usually a better choice for exporting to excel
because it uses a more up to date version of excel.


Jeanette Cunningham -- Melbourne Victoria Australia