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!
