Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
CBurgess
Starting Member
6 Posts |
 Posted - 2004-06-10 : 21:50:46
|
| Hi there,I have an excel file, which has a pivot table derived from data on another worksheet. I would like to know if there's a way to drop and create spreadsheets using SQL tasks and Excel connections in a DTS package. We've done it successfully before, but there are intricasies to it that I'm not familiar with. I know that this can easily be done in script, however there must be an easier way, or is the script approach the most reliable way?Thanks for you help,Chris |
|
|
timmy
Master Smack Fu Yak Hacker
1242 Posts |
Posted - 2004-06-10 : 22:25:38
|
| You can manipulate Excel sheets using ActiveX scripts-SET xl = CreateObject("Excel.Application")SET wbk = xl.Workbooks.Open(yourbookname)wbk.Sheets(n).Delete <-- not sure of the command here. Check out the Excel VBA referenceetcetcetc |
 |
|
|
CBurgess
Starting Member
6 Posts |
Posted - 2004-06-11 : 00:01:44
|
| I was actually wondering if anyone knew of a way to do it using SQL tasks.If you create an Excel connection, and execute a SQL task, using that excel file as the connection, you "should" be able to do stuff like:drop table Datagocreate table Data ...where data is a worksheet. We've got this working on one of our packages, but it's a bit trickier than that, and was hoping if anyone knew the details about it.thanks in advance to anyone who can help out! |
|
|
|
|
|
|
DTS+Excel: Creating and dropping sheets