Resetting Excel Sheet to blank page at the end of each for loop

Hey Guys How can I Reset The Excel Sheet In My For Loop? For Some Reason My Excel Scope Is Not Working, So I Am Using Workbook Excel Activity.

Right Now I Have Clear Data Table Activity, and I Am Attempting To Write On The Sheet By Giving Range of “A1” and the cleared Datatable.

No Errors Occur in this method, but I am not sure why my excel is not resetting but continuously appending new rows through each loop.

@seongchan_cho Can you give us more details of the process, Maybe Screenshots of the workflow or the workflow itself with an example data ?

This is a partial screenshot, but what I m trying to do is I am comparing two excel files and moving the new data into the master data (this activity not shown in the picture).

Because this is a for loop, I will need to clear my x_cel_new datatable and excel sheet with that datatable in order to recieve a new data after an hour ( all the activity is in a while loop that runs every hour).

So at the end of the while loop I am trying to reset all the data inside my excel sheet.

Try this: After using clear Data Table activity for the data table use Write Range and don’t give any range

by no range do you mean :

“”

or

not even “” and just a blank entry?

blank entry

No clears… it still appends same data over and over again because it fails to clear.

@seongchan_cho What if we could Try Deleting the Sheet at the end ? Do you think that would be suitable for your requirement ?

Then I will need to create a new sheet with exact same name “Sheet 2” and “Sheet 3”.
And the order of the sheet ( so old_sheet2 = new_sheet2) should be the same.

Would that be possible?

@seongchan_cho Creating a Sheet with the required name can be done with a Write Range Activity and an Empty Datatable.

But Can you tell me what did you mean by Order of the Sheet ?

since I need to delete two excel sheet. I’m worried if the sheet will shift over.

For instance)
When I delete Sheet 2 → then, sheet 3 will be named sheet 2.

Will this matter?

If I delete both sheet at the same time before I create new sheets 2&3, will this not be affected?

@seongchan_cho How would this happen ? I do not think it would be affected in that way unless you have an macro or vba code that does that operation by default when you perform deletion operation.

how can I delete and recreate the sheet?

Please help me I have been stuck with this problem for hours…

I have tried
delete range
select range+hotkey
write cell with empty entry

both on excel scope and workbook.
None of these methods work…

Please Help me
SOS

@seongchan_cho Check this sample Workflow :
Delete Excel Sheet.zip (9.5 KB)
In the Sample Excel file, there are 3 Sheets, After Execution only One Sheet Will be preserved and the other two sheets will be deleted.

File doesn’t run, it says VBA cannot be ran with programming method

@seongchan_cho Can you post the Error Screenshot?
Also for configuring the excel for the VBA to be executed check the below post.

okay that worked how can I recreate the sheets?

@seongchan_cho Recreating the Sheets would be just using a Write Range with a Sheetname that you need and new System.Data.Datatable as the Datatable value

It returns me:

Invoke VBA: Could not load file or assembly ‘Microsoft.Vbe.Interop, Version=15.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c’ or one of its dependencies.

What’s could be wrong?

Hey @supermanPunch thank you so much for helping me.

I asked a lot of questions in different forms of question on forum and used many different ways, and at last I found the link below to work.

I used studioX activity and it worked perfectly. Thank you!

1 Like