DTS: Export to Excel
I'm exporting a simple table to Excel but my problem is that the table columns will change every month since the column names will list month names (ex. May2009). Can I use ActiveX to dynamically create the transformation source? Please help!
Software/Hardware used:
Software/Hardware used:
ASKED:
June 30, 2009 7:55 PM
UPDATED:
July 3, 2009 4:31 PM
Answer Wiki:
Hello
You can try using the transformation between SQL server to MS Excel. If this doesnt work
than use ActiveX script to generate the MS Excel file which will able to handle dynamic column names.
This will involve writing macro which will select the required columns from table in SQL and put the same in MS Excel file and save the same with new name each time.
This macro will be called from Active X script which will run through DTS.
If you need further help, please do write. I will let u the activeX scripts to run the macro.
Regards,
Sarfaraz Ahmed
<a href="http://findsarfaraz.blogspot.com">MS Excel help</a>
To see all answers submitted to the Answer Wiki: View Answer History.
Thanks for suggestions. I have used Active X to dynamically write the new column names into the Excel file. My problem is in the transformation for the Source table. This table has new columns every month so next month when I run it, I will have to re-do the transformation (source table) for the new column names. I don’t want to do this every month so how do I dynamically create the transformation so that it sees the new columns in the table?
Thanks!
Figured out a solution… The number of columns in the table will remain the same so I can copy the data to a temp table that will have standard column names and use this table in the transformation.