Clear out Excel File with Workbook activities

Help Studio
, ,
1

Hi every I have a problem; we have an excel file with headers and three tabs. Currently the bot clears out the three tabs, other than the headers, overwrites data in the rows for each tab, and then saves it as a new file.

We are not going to have excel in our environment anymore so I need to do this now with workbook activities. I can write the range very easily, but I don’t know how to clear out the three tabs (leaving headers). For example, if the previous run has 20 rows on the first tab and the next run of the bot only has 10, I need those remaining 10 from the first run to not be included, but with the write range activity alone those will still be included.

2

Hi @cssc

Use read range workbook instead of using excel activity and perform the specific actions and write using write range workbook

It helps!

3

That was the plan. Will write range with the workbook activities clear out excess rows that are beyond the current bot run’s data set?

For example if run 1 has 20 rows of data, and run 2 has 10 rows of data. When I write the range in run 2 it will only fill the first 10 rows in the excel file, what about the second 10 rows that still remain from the prior time the bot ran?

4

My understanding is that you need to clear contents of all sheets in your excel file except headers without having excel installed. One way is to use c#/vb code I can share sample vb.net code which can be used inside invoke code activity

5

Hi @cssc

One way to read the excel of both sheets using Read Range Workbook and store bothe the sheet data in data table.

After that you can use Clear Data Table to clear both the data table without losing the headers.

Hope you understand!!
If you need more clarification specify your input so that we can come up with an logic.

Regards