As I am a new user, I am unable to attach any sample excel files for reference. The difficulty that I am facing is this. I have an excel sheet that looks something like that:
I am looking to split this worksheet into 3 different workbooks (i.e. All the answers under ABC in Workbook 1, All the answers under DEF in Workbook 2, All the answers under GHI in Workbook 3.)
As this is a daily report, the number of answers (i.e.Yes/No) under each “Name” will be different everyday. Is there a way to use UIpath Studio to find the specific “Name” first, copy everything until -Page Break-, paste into a new workbook, and repeat with another specific “Name”?
Oh @WadeShon you are new user I didnt see sorry, welcome then, that easyupload access denied ok, just take a screen shot of one sheet And do the pasting here
Yes correct. Usually what I do is i will set the range up to specific cells and then copy to a new worksheet.
However, it will not work this time round, because I am getting a different worksheet daily. What i mean is for example, today CHARIZARD have 6 rows and BLASTOISE have 12 rows to copy over. Tomorrow it may be 18 and 26 rows, the next day may be 32 and 48 rows, so on and so forth.
So I am thinking if there is a way to copy everything from CHARIZARD down to page break, paste it on a new workbook, then continue the same for BLASTOISE.
The logic what needs to be done for creating these 2 sheets is
For Charizard: - do read range (dtCharizard) with a table in between CHARIZARD and BLAISTOIS keyword row index value. i.e A(rowIndex_CHARIZARD): M(rowIndex_BLAISTOISE - 1)
For Blaistoise:- do read range (dtBlaistoise) with a table in between BLAISTOIS and end of the excel rows) i.e A(rowIndex_BLAISTOISE): M(TotalCountOfDt)
Finding the rowIndex_CHARIZARD, rowIndex_BLAISTOISE and TotalCountOfDt
Assuming dtMain is datatable of your main excel after doing a read range.
I have tried your previous method and it works for the sample file, but i realised that it will not work for my actual process. Let me explain with an attachment.
Every day, this report will be generated, and you can see that there is Game 1, Game 2 etc, up to Game 13.
However, it is not guaranteed that there will be Game 1 to Game 13 in the report every day. On certain days, some Games may be missing. As you can see in my excel file, Game 3 and Game 4 is not inside.
My task is to find each Game, copy everything until the break, paste into a new workbook and save to a desktop folder, and repeat.
If there is a missing Game for that day, the script should skip that Game and continue running for the rest. I was thinking of looping but not sure how to do so.
Please find this xaml file , just store to a folder and run main.xaml it will generate all game tables inside OutputFilesGames folder. Anotations written inside will help you to understand the workflow
Thanks for the solution. I tried running the script and it works! But i was just informed of new requirements again .
Instead of generating all game tables immediately, I will need to follow this template: Template.xlsx (12.4 KB)
That means i need to generate the game table inside the template, exclude:
Column M (as the template has a new column M)
Game Title (i.e. Game 1, Game 2 etc.)
Game Reference (i.e. 101A, 102A etc.)
In gist, is to copy from S/No onwards, excluding the 2 rows above which contains the game title and reference.
After generating the game tables inside the template, i will need to password protect the file. The password needs to be read from an excel file: Password.xlsx (9.2 KB)
Once it is password protected, then it is saved to the OutputFilesGames folder.
This will be the whole process flow. Sorry for the trouble caused, and really appreciate your help in this
@WadeShon Check this Workflow :
I think I was able to use the template and Append the Data Starting from A7. The Splitting and recognising of different tables in Excel is working based on the Game Number (Game 1, Game 2 etc…) So this is a required data in the excel to separate.
I have not used any extra Activities for this, only UiPath related activities.
Check the Output Folder Execute the Workflow. All files should be generated in the Ouptut folder.
If it is not the expected one, please explain a bit more in detail. Split Excel Tables Into Separate Sheets.zip (31.3 KB)
Also This does not Protect the Excels using Password. There are a few ways of making it possible using Balareva activities such as below one :
I have used +2 to start from SLno row because your rowIndexOfGameStart will give the row index of GAME keyword.
Read range your Password.xlsx and name it as dtPassword and use link query to
getPasswordForSheet by sending Game=game1 or game2 etc which you get the name in the loop
Now you have fetched the Dt of the game then do write range starting cell with A7.
While saving excel protect the using the password you have got in step 2. Protecting of excel you can try if you get any solution in other forums by using custom activities or using VB.net code
You try it, If you still wanted XAML file then let me know I will implement and send you.