Overwrite all Excel data in sheet with datatable that has less rows than the existing worksheet

[Dave (David Wiebelhaus)]

This seems like it’d be a common question, but I didn’t have luck finding it. I apologize if I missed a previous question addressing this issue.

I have an excel file with data that I’m using as a transaction list. I am reading the excel data, processing each row, updating the row with information, and deleting the row if specific criteria is met. After doing this, I’m left with an updated datatable that has less rows than the excel sheet I originally had.

Is there an easy way to overwrite all data within the excel sheet? Simply doing a write range will only overwrite the rows the datatable is pasted into. For example if I start with 100 rows in a worksheet, delete out 20 rows from the uipath datatable, then paste back into the worksheet I will still have 100 rows in the worksheet.

EDIT: A workaround is to add to a different sheet and delete the old sheet, but I am wondering if there is a better way as deleting a worksheet involves invoking VB.NET code or using an excel macro which I’m trying to avoid if possible

[reda (Reda_Marzouk)]

Hey @Dave
you can use a delete range activity and then write range the new one.
my question is why wouldn’t you create a new excel that way you can keep a trace of the old one?

[Dave (David Wiebelhaus)]

I just updated the excel activity pack for the first time in a very long time and just noticed all of the new activites, thanks! I want to keep the existing formulas and whatnot, so I’m thinking the best approach is to store the row count of the excel sheet, do the processing, then do a row count of the datatable again. Then just delete only the excess.

As for why I am not keeping two files, I’m just following what the business requests, I don’t make the rules

[reda (Reda_Marzouk)]

I feel you @Dave when it comes to following business request, anyway if you have formulas in excel then you should probably do as you said.