Hi there,

If I understand correctly, you want to export information into one excel file with multiple sheets.

The way I have done this is as follows:

1. Create an empty Excel file with the sheets in it you need (my example sales.xls with sheets "sheet1","sheet2")

2. Copy empty file to desired location/name

copy d:\templates\sales.xls d:\export\sales.xls

3. Using your select statement to get the desired information for sheet1; insert the data into the 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

4. Using your select statement to get the desired information for sheet2; insert the data into the 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

A few things to note here:

- When you make the template file, make sure to change the column format to whatever you need (especially important if the information contains decimal point information)

Excel uses the standard format and could drop some stuff like that.

- The only change made for point 3 and 4 was the select * from sales_part* - You can select whatever you want!

- The only change made for the select * from [Sheet?$] from the openrowset was the sheetname (Sheet1 to Sheet2).

- The sheet must be declared the same way as described (in square-brackets and with a dollar behind the actual name of the sheet)

- You have to have the Jet 4.0 Driver installed (32 Bit SQL 2005 has this automatically, up till now 64 Bit Jet 4.0 does not exist)

I have a complete example of the described scenario as a sp. If you need to see how it all works together in real life, just let me know and I can post it here. It is far from perfect (e.g. it uses xp_cmdshell), but I did write it in the first 2 months of ever using a Database, so I think that's ok 😀

I hope that helps you out.

Regards

GermanDBA