473,837 Members | 1,561 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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 9241
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******@hotma il.com> wrote in message
news:11******** *************@z 14g2000cwz.goog legroups.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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
5534
by: Jeff | last post by:
I will start off by giving you a background of the process I am taking. The nature of my DTS package is that I recieve an Excel Spreadsheet, run it through the DTS Package applying validation to the records, create an excel file that contians the records that do not pass the validation, send this Excel file back to the person who sent me the original spreadsheet. They will send the Excel Spreadsheet back once they correct the records. ...
1
6267
by: richilli | last post by:
Hi Any help on this would be appreciated cos its driving me insane. I have a function in VB.NET that takes in an excel range and tries to delete rows where the first column starts with a string. Only it doesnt work and all i get is "Delete method of Range class failed" whatever i do. Any suggestions?
6
16121
by: Karl Richards | last post by:
I am attempting to delete duplicate rows in a spreadsheet using the Excel object. Does anyone have any idea how to do this? I've looked everywhere that I can find on the Web and have not been able to find anything on it. What I have so far is: Excel.Range range = oSheet.get_Range("A1", Type.Missing); range = range.get_End(Excel.XlDirection.xlDown); string downAddress = range.get_Address(false, false, XlReferenceStyle.xlA1,...
11
2577
by: Tempo | last post by:
Hello. I am getting the error that is displayed below, and I know exactly why it occurs. I posted some of my program's code below, and if you look at it you will see that the error terminates the program pre-maturely. Becasue of this pre-mature termination, the program is not able to execute it's final line of code, which is a very important line. The last line saves the Excel spreadsheet. So is there a way to make sure the last line...
13
6971
by: Shelley | last post by:
Compare Current Year Worksheet with Previous Year Worksheet and if SSN exists in Current Year Worksheet & Not in Previous Year - Copy this Row from Current Year Worksheet & Paste into Previous Year Worksheet Compare Previous Year Worksheet with Current Year Worksheet and if SSN exists in Previous Year Worksheet & Not in Current Year Worksheet - Delete this Row out of Previous Year Worksheet - THIS IS WHERE I'M HAVING TROUBLE. This is the...
8
10113
by: LimaCharlie | last post by:
Hi, Question pls. I have an MS SQL local package where it exports data from SQL table to Excel file. My question is, how can I erase all the records in my excel file before i export the new data from SQL table? What i want is to delete the rows in the destination file before inserting new records. Note: I cannot do the drop & recreate table because the users has no permission to do this in the database. Only the DB admin is allowed...
1
7923
by: Krishna | last post by:
I want to delete some rows (by creating a loop may be) using xlrd. Is this possible, if not how do I do that with python? Please help Thanks Krishna
5
4967
by: N2Deep | last post by:
I'm new at trying to write VBA, and have been working on this delete loop for a while, and could use some help. I want the macro to start in cell B1, if it finds the string "Contract Type" in cell B1 I want it to delete that entire row and exit (Contract Type will only ever appear in cell B1). If it doesn't find string "Contract Type" in above, I would like it to move down the B column one cell at a time....deleting all rows with an empty...
8
2987
by: Michel Esber | last post by:
Hello, Env: DB2 V8 LUW FP16 running Linux create Table X (machine_id varchar(24) not null, ctime timestamp not null); create index iFoo on X (MACHINE_ID, CTIME) allow reverse scans; alter table X add primary key (MACHINE_ID, CTIME); Our C++ application inserts data into a table X using CLI array insert
0
9683
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
10883
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
10276
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
9409
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
7813
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
5670
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
5851
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
2
4049
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
3126
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.