If you are fighting with Excel, this doesn't have error trapping for when the table DOES NOT exist but (if you're not too concerned about that scenario happening), this works pretty well without activex...

Using normal DTS Execute SQL Tasks (connected with On Success workflow):

Task 1

a) Set existing connection to your XLS connection

b) Drop table mytablename

(Refer to tablename box on destination tab on transform data task. This actually drops the table from within excel which is actually a 'defined name' space in excel speak)

Task 2

a) Set existing connection to your XLS connection

b) CREATE TABLE `mytablename` (

`Column1` VarChar (30) ,

`Column2` VarChar (40) ,

`Column3` VarChar (3) )

(Copy your create table statement from your transform data task, destination tab, Create button.) Connect Task 2 to your SQL connection and continue on with transformation like normal.

Good luck!