SQL Server Performance Forum – Threads Archive

DTS Package Exporting to Excel question

Hey guys this is my first time on this board so bear with me if you will….. I have a fairly basic DTS Package that when all is said and done exports a table to an Excel spread sheet. ( I do have some other stuff in there with the file system object and stuff) Anyway here’s my problem. The excel sheet gets exported no problem, and all the data is correct. What I want to do is then take that excel sheet and do some basic formatting on it. (bolding and centering the header row, autosizing all the columns and freezing the header row) I saved all of this into a macro, but can’t seem to find a way to use this macro inside of the package so when this report is run daily, the sheet is automatically saved with the new formatting. Can anyone help me figure out a way to get this sheet formatted the way I want it, without me having to physically do it myself every morning. Any help or suggestions would be great! Thanks TJ

I never tried using macro in excel but the alternative I can think of is you can call the Excel objects from within a DTS ActiveX script, make any changes to you Excel sheet that you want. Although it is probably easier to create a "template" Excel workbook, copy it at the beginning of
your DTS package and export your data to the copy. HTH Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

<blockquote id="quote">quote:<hr height="1" noshade id="quote">Originally posted by satya Although it is probably easier to create a "template" Excel workbook, copy it at the beginning of your DTS package and export your data to the copy. <hr height="1" noshade id="quote"></blockquote id="quote"> Can anybody tell me a link where I could find how to do that ? Thanks a lot! [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />]

]]>