Invoke Method to Clear Contents from Excel (not deleting rows)

excel
uiautomation
activities

#1

Could anyone please suggest an Invoke method to clear the contents from Excel for the selected cells.

Currently we have the Invoke method for deleting rows. But it would be more helpful if we have one for clearing the contents from Excel.


#2

@Jayendran

My suggestion is you can Assign an empty value to the cell by using Assign activity.

Regards,
Mahesh


#3

Could you please assist me with an example. But clear contents would be the best option to clear and update the details using write range for larger amount of data.


#4

@Jayendran

Suppose you are having a datatable dt with 5 columns.
and you want to clear 6 th row of 5th column then you can do like this.
In assign activity
dt.Rows(row index)(column index)=string.Empty

here row index in our example is 6 and column index is 5, you can also use column name inplace of column index.

Regards,
Mahesh


#5

If we have removed 3 rows in between and we use a write range, it will override the existing data in the Spreadsheet. Instead, if we have the Invoke Method to clear contents, the write range function will go perfect.

So, if anyone have any idea of creating clear contents in Spreadsheet using the Invoke method will help the users in manyways


#6

@Jayendran

Then can you just eleborate your requirement

Regards,
Mahesh


#7

I have used write cell activity with range of the cells to be cleared with value as ""
This automation making the excel sheet corrupt.
Did you noticed the same or your solution works fine using assign activity?


#8

@priyanka.yadav18

First read the excel sheet and store it in a datatable.
Then run for each row and use assign activity to assign the cell value as empty.
Then use write range activity to print the updated data table in excel sheet . it will work

Regards,
Mahesh


#9

Thanks if required I will try this now.


#11

Yes, if possible please check and share us an Invoke method which clears the data in the excel spreadsheet as same as the ClearContents function in VBA.


#12

How to Clear Excel Sheet Cell values. Refer this link. Write cell option