Export a SQL Server stored procedure to an Excel workbook with multiple sheets

Question

How can I export an Excel workbook from a stored procedure to multiple sheets with multiple sql statements?

I am currently using the following statement:

EXEC proc_generate__excel 'db', 'temp',@filename, @SeqNo, @ext, @sqlorder 

It will create three Excel workbooks if there are three sql statements.

How can I export data from three sql statements to three sheets in one Excel workbook ?

Answer 1

• Create an empty Excel file with the necessary sheets (my sales.xls example with sheets "sheet1", "sheet2")

• Copy the empty file to the desired location / name

• Using the select statement to obtain the desired information for sheet1; paste data into excel file:

   insert into OPENROWSET( 'Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=d:\export\sales.xls;;HDR=YES', 'SELECT * FROM [Sheet1$]') select * from sales_part1 

• Using the select statement to get the desired information for sheet2; paste data into excel file:

   insert into OPENROWSET( 'Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=d:\export\sales.xls;;HDR=YES', 'SELECT * FROM [Sheet2$]') select * from sales_part2 

See these links for reference:
http://www.sqlservercentral.com/Forums/Topic487837-19-1.aspx
http://www.sqlservercentral.com/Forums/Topic660148-338-1.aspx
http://www.databasejournal.com/features/mssql/article.php/10894_3331881_1

Some streams of SO:
Export SQL Server to Excel with OPENROWSET
error in sql script with 'openrowset'