I don't think it is working. I tried it. Even though the DTS package executes with no errors, nothing happens - the original data remains in the excel spreadsheet.
The puzzling part is that it executes successfully. I tried just having the DROP TABLE component to see if the contents of the Excel spreadsheet would be deleted ... although the package executed successfully, the contents were not deleted.
Hmm.... I am not a DTS programmer any more... so I am working from memory here... let's see...
try these little debugging thingies...
start with a fresh workbook. Make sure it has only one page in it. run the create table statement pointing at that one page. close the connection. go back and run the delete table statement.
i think the connection object can point to either a page or a range, and that the "table" is really a contiguous range. so play with that idea. if the create table didn't work before, try creating a range now, and change the connection to this range now instead of the page.
as i recall, the default setting for excel is not to close the connection which leaves the workbook in a read only state. so don't forget to change that setting.
while you are messing around with this, between runs, try opening the workbook, deleting every freaking row (rightclick delete - you know what i mean) - that clears any remembered range. And if the create range thing worked for you before, create your range again with only the column headings and no, or one, row in it.