April 28, 2004 at 2:22 am
I'm making a DTS package (sql 2000) to put some small tables directly into Excel spreadsheets, but each time I run it, it appends to the rows in the spreadsheet. If I dumped them to a .csv, it would delete first, but I can't find any way to either delete the sheet, clear the sheet, or even delete the entire .xls
Any ideas ?
April 29, 2004 at 12:42 am
use first a DTS Step vb script where you delete the previous Excels
and on completion create en fill the excels
April 29, 2004 at 4:28 am
After you have set the Excel sheet up, create two SQL Tasks. The first to drop the table (Excel worksheet) the second to create the table (Excel worksheet).
You can copy and paste the syntax for the create table by opening your transform data task and click on create where you can copy the code - make sure the table name is correct.
Don't know why but Excel seems to remember the format of the table when you drop and recreate it.
I found an additional step was desirable. In above scenario the file size does not reduce if you drop say a 500 row worksheet and create a 10 row worksheet. The following works for me:
Delete all rows except the titles and first row of data.
Highlight the first row and then click delete. This clears the data but preserves the formatting including custom numbers, conditional formatting etc.
I then use save as to create a template.
The below ActiveX script copies the blank template:
Function Main()
Dim oFso
Set oFso = CreateObject("Scripting.FileSystemObject")
Dim sSaveFile, sTemplate
sTemplate = DTSGlobalVariables("TemplatePath").Value + DTSGlobalVariables("OutputName").Value + "Template" + ".XLS"
sSaveFile = DTSGlobalVariables("TemplatePath").Value + DTSGlobalVariables("OutputName").Value + ".XLS"
oFso.CopyFile sTemplate, sSaveFile, True
Set oFso = Nothing
Main = DTSTaskExecResult_Success
End Function
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply