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 |
|
mikejohnson
Posting Yak Master
153 Posts |
 Posted - 2004-02-16 : 11:54:17
|
| i'm running a dts package to take the results of an sp and put them into an excel document on the server. then the dts package emails out the excel document. the problem i have is the dts package will run on a weekly basis. i don't know how to modify the dts package to delete the data in the excel document before appending the new data. so right now the excel document just keeps getting bigger and bigger with each week's results added. i just want the one week. how can i do this? |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2004-02-16 : 12:08:49
|
| Something like this for deleting all data in all worksheets?Haven't tried a delete but should work.This is for VB - use the commented out bits for vbscriptDim x As New Excel.Application'Dim xDim y As Excel.Worksheet'Dim y 'Set x = CreateObject("Excel.Application") x.Workbooks.Open "d:\mydb.XLS" For Each y In x.Workbooks(1).Worksheets y.UsedRange.Delete Next x.Workbooks(1).Save x.Workbooks.Close Set y = Nothing Set x = Nothing==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
mikejohnson
Posting Yak Master
153 Posts |
Posted - 2004-02-16 : 12:12:36
|
| where can i insert vb or vbscript into a dts package? |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2004-02-16 : 12:16:31
|
| Into an activex task.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
mikejohnson
Posting Yak Master
153 Posts |
Posted - 2004-02-16 : 12:52:23
|
| that worked, but now when the dts package runs and goes to put new results into the excel document the 1st row (with field names) does not have the field names in it anymore so the insert fails saying table does not exist in excel document |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2004-02-16 : 13:02:31
|
| Yep - you've just deleted them.You can add the names in the activex script by inserting the text into the cells (you could delete the spreadsheet and create a new one) or change the delete so it doesn't delete the first row.I'll have look how to do it when I get time==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
mikejohnson
Posting Yak Master
153 Posts |
Posted - 2004-02-16 : 14:14:28
|
| thx, much appreciated. |
|
|
|
|
|
|
appending data problem