Hey eb
I am clearing the contents of one sheet using clearcontents().

Then transferring new data to that sheet using dts (data transformation task).

Unfortunatly that data is positioned in the sheet, below the range i had
cleared befor,instead of replacing the empty rows there.

Any idea why??

TIA
Rea

Activesheet.ClearContents
ActiveWorkbook.Save

Excel will hold in memory where the data was before it was cleared so it can
do things like Undo. To prove this, clear a sheet of data and then hit
Ctrl+End. Now hit the Disk icon to save it and do Ctrl+End again.

Mike F
"Rea Peleg" wrote in message
...
Hey eb
I am clearing the contents of one sheet using clearcontents().

Then transferring new data to that sheet using dts (data transformation
task).

Unfortunatly that data is positioned in the sheet, below the range i had
cleared befor,instead of replacing the empty rows there.

Any idea why??

TIA
Rea

You are right! but...
I think i am doing the same in my code.
I clear the sheet, save it and quit the excel application all together:
'clear content of dataRange
dataRange.Offset(2, 0).ClearContents
xlWb.Save
xlWb.Close
xlApp.Quit

Only then I continue to the next step in my dts package - where i do the new
data
insertion using data transformation task.
From some reason that sheet behaves as if it was not yet saved after being
cleared before..

Thanks alot
Rea



"Mike Fogleman" wrote:

Activesheet.ClearContents
ActiveWorkbook.Save

Excel will hold in memory where the data was before it was cleared so it can
do things like Undo. To prove this, clear a sheet of data and then hit
Ctrl+End. Now hit the Disk icon to save it and do Ctrl+End again.

Mike F
"Rea Peleg" wrote in message
...
Hey eb
I am clearing the contents of one sheet using clearcontents().

Then transferring new data to that sheet using dts (data transformation
task).

Unfortunatly that data is positioned in the sheet, below the range i had
cleared befor,instead of replacing the empty rows there.

Any idea why??

TIA
Rea

shouldn't

Dim x As Integer
x = Activesheet.UsedRange.Rows.Count instead of ActiveWorkbook.Save

do a reset also?

Dm Unseen

Since you need to write excel code anyway, why do you push the data to
excel with DTS instead of pulling the data with
Queries/QueryTable/PivotTable/ADO ?

Because it's easier..
Any way did it now with ado and vbscript
and it works fine.

"DM Unseen" wrote:

Since you need to write excel code anyway, why do you push the data to
excel with DTS instead of pulling the data with
Queries/QueryTable/PivotTable/ADO ?