469,954 Members | 1,722 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,954 developers. It's quick & easy.

Delete rows in Excel From DTS package

I have a DTS package that needs to refresh data in 3 separate Excel
spreadsheets on a daily basis. The problem is that unless I manually
delete the previous day's data, it appends rather than replaces.

I can't delete the excel files on a daily basis, as they have to be
there for the DTS package to be able to export to Excel. What I want
to do is create a VBScript (ActiveX Control) to delete all the rows of
data except the first row within each spreadsheet as the first step of
the DTS package. Then the remaining steps would run and the
spreadsheets would only have the current day's data at the end of the
process.

Thanks for any help offered.

Jul 23 '05 #1
1 8936
I would recommend using a pull rather than push strategy with Excel.

Have your DTC package deposit the data in a report table and then embed
queries in the Excel spread sheet to grab that.
Your users could just hit the refresh button on their spreadsheets to get
the latest and greatest data (or write VBA script in the open even to
refresh it auto-magically).

You may be able to invoke the Excel App from the DTC script to get it to
call the refresh function as well.

We had the same issue with exporting to Excel. My crackpot theory was that
it was using the Excel ODBC driver, which appears not to be able to rewind
when streaming data into a spreadsheet. You may be able to delete the rows
to reset the spreadsheet in a separate operation from adding the new rows,
or do what we ended up doing - having DTC do a file-copy to overwrite a
template on-top of the target spreadsheet.

Happy Trails ...

<sm******@hotmail.com> wrote in message
news:11*********************@z14g2000cwz.googlegro ups.com...
I have a DTS package that needs to refresh data in 3 separate Excel
spreadsheets on a daily basis. The problem is that unless I manually
delete the previous day's data, it appends rather than replaces.

I can't delete the excel files on a daily basis, as they have to be
there for the DTS package to be able to export to Excel. What I want
to do is create a VBScript (ActiveX Control) to delete all the rows of
data except the first row within each spreadsheet as the first step of
the DTS package. Then the remaining steps would run and the
spreadsheets would only have the current day's data at the end of the
process.

Thanks for any help offered.

Jul 23 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

6 posts views Thread by Karl Richards | last post: by
1 post views Thread by Krishna | last post: by
8 posts views Thread by Michel Esber | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.