DTS to truncate excel and load fresh data

I have done DTS that export data from SQL to .xls, it works perfect, my problem is my table from SQL get truncated everytime before i load data but my .xls file always come with previous records which I don't want. i.e. if my Sql table had 3 rows , when i finish to execute the dts, my .xls come with 3 rows, when I exec again, my table get truncated and my .xls add another 3 rows. How can I solve thisCan you try again?

I'm not sure I follow you...

Can you put the steps you do down in bullets...and what the final; result would be

1. I DTS in to a table
2. ect

I expect: x|||You want to "truncate" the records in the excel spreadsheet... correct ?|||1 - I truncate TableA in SQL
2 - I select few fields from TableB and insert into TableA
3 - I select everything from TableA and extract it to .xls (here i need to clear all fields in this .xls if there was any so that I can populate this .xls with new results)|||Thanks I got the answer. One more problem, when I drop the .xls table everytime I create it again and load it with data then it leave cloums which had last data and put data on the last line. Lets say I had 4 lines before and dropped the table and reated it again, and load data, it will load data from the 5th line onwards, how can I resolve this|||I'd like to know how you managed to truncate your Excel file. When I execute my DTS package, data keeps getting added to the Excel file, appended on to existing data. I want to wipe everything clean first. How do you do it?|||I'm also facing the same kind of situation..plz let me know if you've found a solution|||I have done this by recreating the Excel spreadsheet. The following VBScript code should do this for you.

'************************************************* *********
' Visual Basic ActiveX Script
'************************************************* *********

Function Main()
Dim oFSO
Dim xlApp
Dim wkbNewBook
Dim strBookName
Const xlNormal = -4143
Const READ_WRITE = -1

' ****************
' Remove existing Excel Workbook
' **************
Set oFSO = CreateObject("Scripting.FileSystemObject")

If Not oFSO.FolderExists("C:\folderName") Then
oFSO.CreateFolder ("C:\folderName")
End If

If oFSO.FileExists("C:\folderName\FileName.xls") Then
oFSO.DeleteFile ("C:\folderName\FileName.xls")
End If

' ****************
' Create new Excel Workbook
' **************

' Create object
Set xlApp = CreateObject("Excel.Application")

' Add new workbook to Workbooks collection.
Set wkbNewBook = xlApp.Workbooks.Add

' Specify path to save workbook.
strBookName = "C:\folderName\FileName.xls"

' ****************
' Format new Excel Workbook
' **************
With wkbNewBook

.Sheets(1).Select
.Sheets(1).Name = "SheetName"
.Sheets(1).Range("A1").FormulaR1C1 = "Field1Name"
.Sheets(1).Range("B1").FormulaR1C1 = "Field2Name"
.Sheets(1).Range("C1").FormulaR1C1 = "Field3Name"
.SaveAs strBookName, xlNormal ,,,READ_WRITE
.Close
End With

Set wkbNewBook = Nothing
Set xlApp = Nothing

End Function

##############################33

Originally posted by msenoelo
I have done DTS that export data from SQL to .xls, it works perfect, my problem is my table from SQL get truncated everytime before i load data but my .xls file always come with previous records which I don't want. i.e. if my Sql table had 3 rows , when i finish to execute the dts, my .xls come with 3 rows, when I exec again, my table get truncated and my .xls add another 3 rows. How can I solve this