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

Help Forum
, , , ,
1

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.

2

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

3

ans
ans812×529 18.8 KB

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.

4

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

5

by no range do you mean :

“”

or

not even “” and just a blank entry?

6

blank entry

7

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

8

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

9

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?

10

@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 ?

11

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?

12

@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.

13

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

14

@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.

15

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

16

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

17

okay that worked how can I recreate the sheets?

18

@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

19

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?

20

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