DTS Automation - Huge Problem

[maxvalery]

Hi,

I have a DTS package for SQL Server 2000. Its entire purpose is to

1) run a parametrized stored procedure
2) run a query
3) EXPORT parametrized stored procedure into an Excel file NAMED
according to the query from #2
4) REPEAT until reached last row of the query, then run last row and
finish DTS package.

I've implemented everything EXCEPT for #3.

The problem I'm having is after setting the Excel export connection's
destination path dynamically with

DTSGlobalVariables.Parent.Connections("Excel").Dat aSource = "C:\" &
DTSGlobalVariables("subgroupPATH").Value & ".xls",

the DTS errors out: "The Miscrosot Jet database engine could not find
the object 'TempTable'. Make sure the object exists and that you spell
its name and path correctly'.

When I go back, double click the silver transformation arrow between
SQL Server and Excel, and create the destination table manually and
specify name and transformations, the DTS runs ONCE, but produces the
above error on the second loop.

Please reply if you know of a way to solve my issue or set the temp
destination table programmatically!

Thanks.