Export SQL stored procedure to Excel file

[dbushcmohle]

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

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" 

I could be totally wrong, but here is what I think is happening... When the
acOutputStoredP rocedure 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

P.S. - I am working in Office 2007...

[NeoPa]

The only thing I can think of is to have a QueryDef that executes the code (you can update its SQL before the export) and use the QueryDef in the OutputTo command. A kludge, I know :(

[dbushcmohle]

Hello,

I wanted to let everyone know that I FINALLY found out how to do it... Below is my final code... Hopefully it can help someone else in the future...!

Thanks,
-Dave

Dim strDate1 As String
Dim strDate2 As String
Dim sqlSP
 
strDate1 = Forms!frmDailyCounts!cntrl_Calendar.Value
strDate2 = Format(strDate1, "yyyy-mm-dd")
 
sqlSP = "EXEC pDailyCounts @CalDate='" & strDate2 & "'"
 
DoCmd.OutputTo acOutputStoredProcedure, sqlSP, "Microsoft Excel (*.xls)", "c:\dbase\DailyCounts.xls"
 
xlApp.Workbooks.Open "c:\dbase\DailyCounts.xls"

[NeoPa]

Dave,

What is the name of the worksheet created when this code executes? I'm surprised this works, but good for you for finding this solution :) It's less of a kludge than mine.

-NeoPa.