Export to multiple Excel files with multiple sheets

[jymm]

Seems that this group has been down this road before, but I can not find the final solution.

Essentially I have to create multiple output excel (or csv) files from a query/t-sql/dts/bcp (I don't care). Each of those excel files will have multiple sheets (tabs) with the name and other data being pulled from the query.

ne1 got a good direction to go for this?

Thanks in advance!

 

[jgoodman00]

Use DTS to do this. If you export with DTS to an already existent excel file, it will add the worksheet. The only problem comes when the worksheet already exists.

I use this method to export the results of approx 20 queries into a single, multi-worksheet, workbook.

James Goodman MCSE, MCDBA

http://www.angelfire.com/sports/f1pictures

 

[jymm]

ok James --- I am experienced with DTS, but have never had to export to more than one sheet at a time --- can you elaborate just a titch or give me a gental shove in the right direction???

 

[jgoodman00]

If you can export to one sheet, you can export to multiple worksheets.

It is as simple as running the wizard, & giving the target file the same name. It should then add the required table/query results as a worksheet to the specified workbook.

The only time this trips over itself, is if the worksheet already exists. I generally delete the old file (using standard VB) if it already exists, & then write the data into a new file...

James Goodman MCSE, MCDBA

http://www.angelfire.com/sports/f1pictures

 

[jymm]

ok James --- one more time for the left ear this time...

so I have my query

have my excel file

go into the wizard, but no place can I see a place where I can create a new tab in that excel file for each (pick a variable) from the query ---

maybe I am missing it - maybe I need more caffiene - maybe John Kerry will get a nipple ringy since George bush already has a Dick Cheney ---

in any case I am lost while looking...

 

[jymm]

Hi - I am Jymm and I am stupid --- (Hi Jymm!)

ok so I spent the majority of my time since the last post looking at ActiveX and DTS, Looping in DTS, and the file system objects. I got SO close a number of times. Then on a whim I started looking at Crystal & the export from there. Using a utility that I bought from

http://www.MilletSoftware.com

I was able to create one excel file for each Sales Person with Tabs for each Customer.

Granted I learned alot about ActiveX & DTS while looking, but hopefully this note wil help someone else avoid the pitfall and not re-invent the wheel - setting this up took less than 1 hour... sheesh...