sql server dts: Client OS: Win2K
Client Excel Version: 2000
SQL Server: Version 7.0

I'm attempting to populate an Excel 2000 spreadsheet with
the results of an already created DTS package that uses a
SQL script to generate the result set. The DTS package
currently runs successfully, populating the targeted Excel
spreadsheet when launched from Enterprise Manager.

However, I want to execute the DTS from within an Excel
macro. I've coded a macro exactly as stated in KB
article - 306125 "HOW TO: Import Data from Microsoft SQL
Server into Microsoft Excel", and changed the necessary
elements to reference my database and such. This works
beautifully when I tested selecting from a SQL database
table.

Now, I wish to instead have this macro launch the DTS
package I have instead of doing a .Open "SELECT
<enterstatementshere>" command as displayed in the KB
article. Is this possible?

If so, how do I reference the DTS package within the Excel
macro? Thanks for any help.

Sib

In article <045701c36cd5$7ac94330$a301280a@phx.gbl>, Sib
<poxster@mindspring.com> writes
[quoted text, click to view]


DTS can pump data directly into an Excel sheet. (A sheet is like a
table.) There is no need to use a macro at all if using DTS, just create
and run the DTS package. Use the Import Export Wizard to get started.

You can use DTS as an OLE-DB provider by checking the property "DSO
rowset provider" for a suitable step such as a DataPump task, but that
can get messy. There is more information on this option in Books Online,
but it is not widely used.


--
Darren Green (SQL Server MVP)
DTS - http://www.sqldts.com