Re: Formatting an Excel *** from a DTS package

From: Rick Razzano (rrazzano_at_netzero.net)
Date: 03/12/04 

Date: 11 Mar 2004 17:15:09 -0800

One solution may be to use Excel's XML format. You could create a
package that dumps the table to a simple XML data file, then use an
ActiveX scripting task in the package to perform an XSLT transform to
make it into an Excel XML file.

Alternatively, you might be able to use FOR XML, EXPLICIT to go
directly to Excel XML schema from SQL Server, but that might be a
little tricky and/or ugly.

MS has their Excel XML format documented here:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnexcl2k2/html/odc_xlsmlinss.asp

hope that helps
Rick

tjdalton311@hotmail.com (TJ Dalton) wrote in message news:<af73050.0403111201.7136870c@posting.google.com>...
> I have a fairly basic DTS Package that when all is said and done
> exports a table to an Excel spread ***. ( I do have some other stuff
> in there with the file system object and stuff) Anyway here's my
> problem.
>
> The excel *** gets exported no problem, and all the data is correct.
> What I want to do is then take that excel *** 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 *** is automatically
> saved with the new formatting. Can anyone help me figure out a way to
> get this *** 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