VBA - OutputTo code issue

I have a report that needs to be exported into excel.
I can export it manually, file->export and it exports it fine. What I want to do is export it when the user clicks on a button.
I have created a form with a button "Export".
Here is the code for the button:

Private Sub cmdExport_Click()
Dim stDocName As String
stDocName = "rptClientReport"

DoCmd.OutputTo acOutputReport, stDocName, acFormatXLS, "C:\Reports\ClientReport.xls"

End Sub

and when click the button, I get error " Run time error: 9.
Subscript out of range"
Why cant I export the report automatically, but I can do it manually???? I dont see anything wrong with the actual report.
please help

A good thing to try when you get an error like this is to Google the exact error message, in quotes. You can often find a lot of discussion of the specific error. I found a couple of references that look like they might point to the problem, which is likely to be associated with Excel, rather than Access:

http://bytes.com/forum/thread191743.html
http://msdn.microsoft.com/en-us/lib...519(VS.60).aspx

thanks for those links but I have already googled
the error and was not able to find the fix.
Again, i can manually export the report as an excel
but code gives me error.
any other ideas?

I'm just taking a guess here but you say you're trying to export an object named

rptClientReport

If this is actually a REPORT then you can get all kinds of errors in trying to export because a report is not meant to be exported to a datasheet, it's meant to only be exported as a (for lack of a better word) picture, a pdf, a rich text file, etc. especially if you have absolutely any special sorting/grouping, formatting, and so on. If you want to actually export data to excel you should export the QUERY or TABLE that is running the report.

it is a report and it was created the same way as the other
reports in the very same database and yet, all those other
reports export to excel via code fine. This report can be exported into excel manually with no errors, the error comes
up during the code only, that is why something is weird.
I have tried transferSpreadsheet function you are talking about and yes, it exports the query, but it exports all of the
fields in a report when i want only the ones user selects via checkboxes in a form.

thanks for your help