 | |
Referencing a DTS package object - Excel macro
microsoft.public.sqlserver.dts microsoft.public.sqlserver.dts
 |
| | Thread Tools | Display Modes |
#1
 
| |||
| |||
|
Referencing a DTS package object - Excel macro -
08-27-2003
, 03:57 PM
#2
| |||
| |||
|
|
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.
--
Darren Green (SQL Server MVP)
DTS - http://www.sqldts.com
#3
| |||
| |||
|
|
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. Here is the actual code from the KB article: 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 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 . |
#4
| |||
| |||
|
|
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
| |
Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.