Export a report into excel

I need help with some VBA code please.
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"
'This code does not work
DoCmd.OutputTo acOutputReport, stDocName, acFormatXLS, "c:\Reports\ClientReport.xls"
End Sub

When I click the "Export" 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.
please help
I have already googled the (*^#$(@ out of this error
and still dont have a fix!

I had no problem running your code. What references do you have ticked in your coding?

Hi Allan,
i am using VB for applications, Microsoft Access 11.0 object
library, OLE automation, Microsoft DAO 3.6 object library
and Microsoft ADO 2.5 library
i do not see anything missing

do you have different references than i do?
thanks

Hello Irina

I have the following ticked VB for applications, Microsoft Access 11.0 object library, OLE automation, Microsoft DAO 3.6 object library,Microsoft ActiveX Data Objects 2.1 Library and Microsoft Visual Basic for Applications Extensibility 5.3.

I read a few years ago that you must also have the last item selected when you are using DAO 3.6. I only use the above in my applications and have no problems only coding logic etc. There maybe odd times that I use another reference as required.

Hope this help.

Hi Allan,
I have added the same references as you have and unchecked
the ones you did not have and unfortunately I still get the error.
I will keep working on this, it is an important part of the project.
thanks for your help