I know how to export a table to excel, however I do not know how to export the data to my worksheet which is pretty much a template. In my worksheet I formatted the columns/rows and just need to insert the data via SQL. Can someone point me to a resource? I am already googleing and searching the forums.
I know the DTS Wizard, however it does not let me remove the rows from excel. Basically my excel sheet contains several clumns, which are calculated based on the data placed inside the excel. Its basically a template which needs to be filled everytime. The DTS Export wizard appends the rows to my excel. Any ideas?
I have a very similar proces in production right now. It also emails the results when finished. It's kind of a hack in my opinion, but works very well so far.
Instead of clearing the rows out before putting the new ones in, have a copy of the excel file with no rows in it. Then just cmdshell and copy that template to a new file and then use DTS to put the new rows in. The Excel DTS export adds the rows to the end of the file.
I have a trick to get the Active X commands easily to format the excel file afterwards. I can also post the code to email the results if needed. You will need to have Excel installed on the server.