Export SQL stored procedure to Excel file

[Guest]

Hello,
I am having problems exporting a stored procedure's results to an Excel
file... I've done this many times successfully, but never had to introduce a
variable. Now that I've introduced the variable, I'm having problems. It is
presenting the error listed below on the last line in my code section below.

ERROR: Method ‘Open’ of object ‘Workbooks’ failed

------- MY CODE BELOW
----------------------------------------------------------
Dim xlApp As New Excel.Application
Dim wbk As Excel.Workbook
Dim wksht As Excel.Worksheet
Dim strDate1 As String
Dim strDate2 As String
strDate1 = Forms!frmDailyCounts!cntrl_Calendar.Value
strDate2 = Format(strDate1, "yyyy-mm-dd")

DoCmd.OutputTo acOutputStoredProcedure, "EXEC pDailyCounts '" & strDate2 &
"'", "Microsoft Excel (*.xls)", "c:\dbase\DailyCounts.xls"

xlApp.Workbooks.Open "c:\dbase\DailyCounts.xls"
------- MY CODE ABOVE
----------------------------------------------------------

I could be totally wrong, but here is what I think is happening... When the
acOutputStoredProcedure function runs, it automatically names the sheet on
the Excel file the name of the stored procedure... Now that I've introduced
the variable, it is trying to name the sheet "EXEC pDailyCounts '" & strDate2
& "'"... After I get my error, I can go to the file location, open it, and
it says Excel is attempting repair an invalid sheet name. Once I let it fix
the file, and rename the sheet name to "RECOVERED" the file opens and I can
see all of my results...

Anyone got any ideas on this one...?

Thanks,
-Dave

 

[Guest]

Assign this statement to a string ("EXEC pDailyCounts '" & strDate2 & "'",
"Microsoft Excel (*.xls)") before the OutputTo. Set a breakpoint on the
OutputTo. Then look at the string in debug.

James

DoCmd.OutputTo acOutputStoredProcedure, "EXEC pDailyCounts '" & strDate2 &
"'", "Microsoft Excel (*.xls)", "c:\dbase\DailyCounts.xls"