[dba-SQLServer]Sprocs to Excel

David,

Many thanks for the advice. I have used you suggestion of creating and
outputing the sproc to a table and then outputing the table to Excel. As
my sproc has 15 parameters I though this would be easier to manage.

I have to fiddle around a bit with the sproc as I wanted to use if for
the input to both reports and Excel. So I have just added an extra
parameter which I can then test in the sproc, if false then the sproc
creates a table for use with excel, if true it just returns its output
to the report.

Regards
Tim


-----Original Message-----
From: David Emerson [mailto:davide at dalyn.co.nz] 
Sent: 29 April 2003 20:27
To: dba-sqlserver at databaseadvisors.com
Subject: Re: [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

_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com