Andy,

you cannot open the Excel file (through automation or otherwise) *and*
then try to connect to it as a database. Files opened (in any Excel
instance) are locked for external data access(even read only).

A tip from me: drop DTS. it is extremely hard to debug and maintain.
Try to connect to your Excel file through linked server function of
your SQL server and link the spreadsheet first and try to query the
data directly in SQL

Dm Unseen