[dba-SQLServer]Sprocs to Excel

Tim,

I found that you can do the following -

Dim strSproc as string
strSproc = "Exec usp_RptProductList param1"
DoCmd.OutputTo acOutputStoredProcedure, strSproc, acFormatXLS, 
"ProductList.xls", True

If the parameter is a variable then use

strSproc = "Exec usp_RptProductList " & param1

If the parameter is a string or date variable then use

strSproc = "Exec usp_RptProductList '" & param1 & "'"

One warning though, Excel will name the sheet whatever the value of 
strSproc is.  This means that if it is longer than 31 characters you will 
get an error when you open the excel sheet.  Excel will rename the sheet 
and you can carry on using it but it is annoying if this happens every time 
you create a new spreadsheet.

The same thing happens if you have more than 1 parameter because excel 
doesn't like comma's in its sheet names.  Do avoid this the simplest way I 
found was for the sproc to create a table, run the sproc first, then export 
the table instead.

Regards

David Emerson
DALYN Software Ltd
25b Cunliffe St, Johnsonville
Wellington, New Zealand
Ph/Fax (877) 456-1205

At 29/04/2003, you wrote:
>The following code will output a sproc to an Excel sheet, but does not
>seem to allow for the use of input parameters to the sproc. (watch for
>line wrap)
>DoCmd.OutputTo acOutputStoredProcedure, "usp_RptProductList",
>acFormatXLS, "ProductList.xls", True
>
>If you use -
>DoCmd.OutputTo acOutputReport, "ProductList", acFormatXLS,
>"ProductList.xls", True
>You can pass the sproc input parameters via the input parameters of the
>report, but creating a report just to pass parameters, seems to be a
>waste of time when I don't actually need the report.
>
>Any ideas on how I can pass parameters to a sproc and get the sproc
>output to an Excel sheet would be greatly appreciated.
>
>Many thanks
>Tim Pain
><MailTo:Tim.Pain at AkzoNobel.com>
>
>
>
>_______________________________________________
>dba-SQLServer mailing list
>dba-SQLServer at databaseadvisors.com
>http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
>http://www.databaseadvisors.com