DTS and EXcel

I am currently creating a DTS that will carry out a select from a table and
insert the results into a spreadsheet.
Each sheet has a formula at the bottom that calculates the sum of column A.
In the DTS the first step that I do is to remove all data that was inserted
into the sheet last time the DTS was run: DROP TABLE [Test$]
However, this removes my formula at the bottom of the sheet (cell A65536).
Is there a way of removing all the data from the sheet without also deleting
my formula.
many thanksHey Warren,
I would remove the DROP TABLE statement and use an ActiveX Script such as
this one:
'***************************************
************
' Visual Basic ActiveX Script
'***************************************
************
Function Main()
Dim xlApp
Dim wkBook
Dim sheet
Set xlApp = CreateObject("Excel.Application")
xlApp.Visible = False
Set wkBook = xlApp.Workbooks.Open("C:\test.xls")
Set sheet = wkBook.Sheets("Sheet1")
sheet.Range("A1:D1000").ClearContents
wkBook.Save
wkBook.Close
xlApp.Quit
Set sheet = Nothing
Set wkBook = Nothing
Set xlApp = Nothing
Main = DTSTaskExecResult_Success
End Function
Obviously, modify the Range to suit your needs. Hope this helps
Kevin Bowker
"Warren Hughes" wrote:

> I am currently creating a DTS that will carry out a select from a table an
d
> insert the results into a spreadsheet.
> Each sheet has a formula at the bottom that calculates the sum of column A
.
> In the DTS the first step that I do is to remove all data that was inserte
d
> into the sheet last time the DTS was run: DROP TABLE [Test$]
> However, this removes my formula at the bottom of the sheet (cell A65536).
> Is there a way of removing all the data from the sheet without also deleti
ng
> my formula.
> many thanks