after clearcontents() new data is positioned below the cleared ran
 

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

after clearcontents() new data is positioned below the cleared ran
 

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

after clearcontents() new data is positioned below the cleared
 

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

after clearcontents() new data is positioned below the cleared ran
 

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 ?

after clearcontents() new data is positioned below the cleared ran
 

shouldn't

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

do a reset also?

Dm Unseen

after clearcontents() new data is positioned below the cleared
 

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 ?