Referencing a DTS package object - Excel macro

[Sib]

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

[Darren Green]

Post by Sib
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

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.

[Allan Mitchell]

You can redistribute the dlls using this

Redistributing DTS with your program
(http://www.sqldts.com/default.aspx?225)

Executng a package in Excel will not be too dissimilar to

Execution
(http://www.sqldts.com/default.aspx?104)
--
----------------------------
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

Darren,
Yes, I have the DTS created already and it does work
successfully to populate Excel like I mentioned at the
beginning of my post. I know I can simply run the DTS
myself, but the select few clients I have cannot because
they do not have SQL Server on their machines.
The Excel workbook to which I populate the DTS data
already has intricate macros built to perform several
calculations and formats from the DTS results data. I'm
trying to just add automation to the current macro so that
clients can call the DTS themselves from within Excel
without me having to first launch the DTS and give them
the sheet data myself.
EXTRACTING THE DATA
' Create a recordset object.
Dim rsPubs As ADODB.Recordset
Set rsPubs = New ADODB.Recordset
With rsPubs
' Assign the Connection object.
.ActiveConnection = cnPubs
' Extract the required records.
.Open "SELECT * FROM Authors"
' Copy the records into cell A1 on Sheet1.
Sheet1.Range("A1").CopyFromRecordset rsPubs
' Tidy up
.Close
End With
cnPubs.Close
Set rsPubs = Nothing
Set cnPubs = Nothing
What would I need to change so that instead of having
a .Open "<SQLselectstatment>", I can call the DTS package
to execute? The DTS I have will already populate the
sheet I need, I just don't know how to call it within this
macro code to execute. I was just hoping someone had done
this before and knew how to alter this or maybe refer me
to an article or specific section of help that describes
it.
Sib

Post by Darren Green
DTS can pump data directly into an Excel sheet. (A sheet

is like a

Post by Darren Green
table.) There is no need to use a macro at all if using

DTS, just create

Post by Darren Green
and run the DTS package. Use the Import Export Wizard to

get started.

Post by Darren Green
You can use DTS as an OLE-DB provider by checking the

property "DSO

Post by Darren Green
rowset provider" for a suitable step such as a DataPump

task, but that

Post by Darren Green
can get messy. There is more information on this option

in Books Online,

Post by Darren Green
but it is not widely used.
--
Darren Green (SQL Server MVP)
DTS - http://www.sqldts.com
.

[MR:K]

This is probably the the worst answer I have seen in a very long time.

Using the style of Q: I want to ... A: No you don't
is not very helpful

From http://www.developmentnow.com/g/103_2003_8_0_0_393429/Referencing-a-DTS-package-object--Excel-macro.ht

Posted via DevelopmentNow.com Group
http://www.developmentnow.com/g/