So, I set it up the way originally laid out in this article, then the bean counters wanted date specific files. Well, I found you post and it looks great, but I am a little lost. Currently, I create a 'table' for the excel file, dump and re-create daily. Your suggestion is to create the path in an ActiveX object (like I could pre-define most of the path and then just add in the date part?), how, then do I create the file from that template? This is all a bit new to me, so sorry if I seem obtuse, just not sure how to bring this together. It sounds great, looks like it is exactly what I am looking for, jsut not quite sure how to implement it, so any further help you can give me would be great.
If you are using a transformation to fill your spreadsheet:
Create an Excel template that you always use for the destination. (Just the field names on the worksheet.)
Upstream of the transform, create an ActiveX. Write some VB code that creates the path on the fly based on date, and assign the path to a local var. Copythe template workbook to that path using theFileSystemObject. Assign the local var to a global string var.
After that, create Dynamic Properties Task. Assign the global var to the destination of the transformation.
After that comes the transformation.
If you have the Excel object model on the server, you can do your copy, move with that in the ActiveX script. But it's bloaty. Use the FSO.
If you use an Excel template like this, you won't need to clear the 'table'. Just leave the template empty, and dump your transform to the worksheet by name. You may find that you need to cast some of your fields to varchar to make them look pretty in the output.