Home > IT Answers > SQL Server > DTS: Export to Excel
Stan1001
25 pts.
0
Q:
DTS: Export to Excel
DTS, Microsoft Excel, DTS Import/Export Wizard, ActiveX
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!
ASKED: Jun 30 2009  7:55 PM GMT
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
RELATED QUESTIONS
0
Findsarfaraz
1395 pts.
0
A:
 RATE THIS ANSWER
0
Click to Vote:
  •   0
  •  0
  • AddThis Social Bookmark Button
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
MS Excel help
Last Answered: Jul 1 2009  3:25 PM GMT by Findsarfaraz   1395 pts.
  •   
0
0
RSS - Discussions Help
Discuss This Answer:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _



_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _

Stan1001   25 pts.  |   Jul 2 2009  6:06PM GMT

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!

 

Stan1001   25 pts.  |   Jul 3 2009  4:31PM GMT

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.

 
0