How to Clear Excel file range except headers without using Excel Application Scope

Help Studio
1

Hello,

I am trying to clear the range in Excel file from column B2 till last entry in column P and I don’t want to use Excel Application scope. Can anyone please support to achieve this.

2

@nilesh.mahajan

One way of doing is use Assign activity and Create a temp Datatable Variable let’s say TempVariable

Now write as TempVariable = OrginalDatatableVariable.Clone

Above will copy the schema to the TempVariable

Now use Write Range activity and pass TempVariable this will keep the headers only

Hope this may help you

Thanks

3

Hi @nilesh.mahajan ,

-You can use Read range activity store the excel file.
-Create a Variable of Array of Data row (System.Data.DataRow).

  • Create a Assign activity, Store to the array Variable with a expression toselect the rows you want to delete.
    -Then Loop through with ForEach. And in body set a Invoke method activity by giving Target object as Rows of the whole data table , Method name as Remove.

Hope it makes some help to you,

4

Hello Srini, thnx for help.

I have tried this but unfortunately it is not doing any changes in file, it’s just update the file edit time but no changes happened.

5

@nilesh.mahajan

Instead of same file, you can create another file and check

Thanks

6

@nilesh.mahajan

Also you can try with VBA code

Check below thread for your reference

Hope this may help you

Thanks