August 21, 2006 at 3:07 pm
Hi,
I have around 20 tables that are created weekly. Number of columns is not known (it depends on the data). I need to move these tables to seperate worksheets in Excel workbook.
I think that I need to check the table structure (number of columns) using Activex then create a ddl statement and create a worksheet/table in excel and make transformation and then execute this task using activex. Repeat this for the number of tables.
Any thoughts? Any help will be greatly appreciated.
Thanks in advance,
mojo the learner
August 22, 2006 at 7:32 am
You are on the right track, and I'd give you the code if I could find it, but unfortunately I don't have access to it here at work.
Using a DTS package you'll be able to do several things. Call a proc that loops thru the sysobjects table (and any others needed) so you can determine what tables need to be exported. Call an Active-X control that creates your spreadsheet, loops thru the list of tables to be exported and names each worksheet, exports that data from a recordset to the respective sheet and formats the columns to the data type that you need (int maps to number and text to text...).
You can even have the DTS Email the resulting file to the end user.
Try http://www.sqldts.com for some hints too.
Regards,
Matt
August 22, 2006 at 8:48 am
Thanks Matt.
Can we access the sysobjects table from Active-x script? If so, then can we use existing connection (in the dts package) or I have to use a new ado object and create new connection to the sql server?
Immad
mojo the learner
August 22, 2006 at 9:27 am
1 connection.
Use a proc to read the systable(s) and dump the needed info into a ## temp so it can be read by the active-x, on success call the active-x.
In the active-x,
1. create a spreadsheet
Loop thru the ## temp table
2. create sheet 1 and name it
3. using the data in ## temp create the column headers and set the format for the columns
4. execute a dynamic sql code to retrieve the data from ## temp into a recordset and dump it into excel line
by line
Increment your loop.
Once your done you can even have sql email it to the recipient or ftp the file to a designated location.
Check MS website for the Excel object so that you can properly define all the data types and such. You can really do a lot with Excel from an active-x control. I've set up drop down lists and such, turned text vertical and added comments to cells.
Regards,
Matt
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply