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.  Copy the template workbook to that path using the FileSystemObject.  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.

Voila!