Exporting to Excel 1

[mbde]

Hello all,

The object: I have a dts package that exports an excel file to a temp directory, then I take that excel file and email it out to a mailing list.

The problem: each time I run the dts it appends to the worksheets/tables.

The constraints: you can not use xpcmd_shell, no privledges. Also you can only give each file a unique name iff you remove the file so not to make a mess of the directory.


I tried a delete from [table$] on the excel connection. But get the error message that Delete is not supported through ISAM. And doing a drop table on Table$ messed it all up the sheet is blank but I can't put data back into the sheet.

Any other Ideas?


Thanks in advance.

 

[mrdenny]

Create an ActiveX script to delete the file after emailing it out. (Or at the begining of the script, either way).

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[mbde]

File Scripting object.... thank you

 

[mbde]

Just one thing to note for anyone browsing this post. You have to recreate the Tables/Sheets by calling a create table command for the Excel connection. If you do not then the datapump fails because it can not find destination table.

 

[SQLDenis]

What i usually do is I have a template excel file in a folder

First step copy file to correct destination
run data step
rename file and I am done

repeat next day



Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/