This is of course all possible using the Excel object model. As for DTS.
To export to Excel you need named Ranges/Tables creating in the worksheet.
When DTS does this it names the worksheet not ranges within a worksheet.
Using Excel I can create many named ranges within a worksheet and they will
appear as destination tables in my DTS datapump task. This of course is all
done up front in Excel itself not DTS. Yes you could do it in DTS but this
as mentioned earlier requires the Excel object model. MS will not support
office in a server side unattended scenario.

It is rather like this in reverse

Importing a Named Range From Microsoft Excel
(http://www.sqldts.com/default.aspx?243)



--


Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org