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

[Guest]

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

 

[Mike Fogleman]

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

 

[Guest]

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

 

[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 ?

 

[DM Unseen]

shouldn't

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

do a reset also?

Dm Unseen

 

[Guest]

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