How to read

[Vishal_K]

Open editor, drag your excel from your computer and release over the editor

[WadeShon]

Yup i tried but it prompts that new user cannot upload any attachments. I upload the excel file onto a web sharing file platform instead.

Hope this helps.

easyupload.io

[Vishal_K]

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

[WadeShon]

No worries. Hope this image helps!

[Vishal_K]

So @WadeShon from your excel sheet you want to create seperate two sheets one for CHARIZARD and another for BLAISTOISE

[WadeShon]

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.

[Vishal_K]

@WadeShon

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.

rowIndex_CHARIZARD=dtMain.Rows.IndexOf(dtMain.AsEnumerable().Where(Function(row) row(0).ToString.ToLower.Contains(“charizard”)).ToArray()(0))+1).ToString

rowIndex_BLAISTOISE =dtMain.Rows.IndexOf(dtMain.AsEnumerable().Where(Function(row) row(0).ToString.ToLower.Contains(“blaistoise”)).ToArray()(0))+1).ToString

TotalCountOfDt=dtMain.Rows.Count

Note: Check row index by putting message box and adjust in the range.

[WadeShon]

Thank you so much! What you have mentioned makes perfect sense to me and I believe it can work.

Let me try it out and I will let you know the results. Really appreciate your help!

[Vishal_K]

Sure @WadeShon if that works fine then mark this as a solution

[WadeShon]

Hi Vishal,

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.

Games.xlsx (22.2 KB)

1. Every day, this report will be generated, and you can see that there is Game 1, Game 2 etc, up to Game 13.

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

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

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

[Pratik_Wavhal]

Hi @WadeShon

Here below is the solution for your query. 11 days before same issue has been solved.

Refer the below thread for the same. Just make changes as per your excel file.

Split the Excel Summary Report based on Headers into separate sheets

@Zahid1 Check this Workflow: I had to use Excel Application Scope in order to get the Output needed, as the Workbook activities Read Range resulted in an Error while using it. Also I had to use one extra Package to make the Workflow work. You should install this Package before executing this workflow as it will throw a Missing Activity Error otherwise. [getCell] Check the Summary File, You’ll notice Only one Sheet present. Close the Excel. Execute the workflow. The Sheets will be created w…

Mark as solution and like it

Happy Automation

Best Regards
Er Pratik Wavhal

[Vishal_K]

HI @WadeShon

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

WadeShon.zip (22.7 KB)

let me know if anything else needed

[WadeShon]

Hi @Vishal_K

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:

1. Column M (as the template has a new column M)
2. Game Title (i.e. Game 1, Game 2 etc.)
3. 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

[Vishal_K]

@WadeShon
Your template is blank

[WadeShon]

Hi,

Row 1 - 6 will have certain words/pictures that is relevant to the game (the details on what to use is not confirmed yet, thus i left it blank).

What i need to do is copy S/No to A7, 1 to A8, 2 to A9, 3 to A10, and Record Type into B7, Game Type into C7, so on and so forth, until L.

[supermanPunch (Arpan)]

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

BalaReva.Excel.Activities

Hi Friends, I have included the below new custom components in the package. 1.InsertImage - Inset a image into the excel sheet in specific top and let position. 2.InsertTableFormat - Inset the table format . 3.SetPassword - Can able to set and remove the password to the work book. Namespace [image] 1.InsertImage Properties [image] Activity [image] Samples SetPassword.zip (15.2 KB) 2.InsertImage Properties [image] Activity [image] Sample : InsertImage.zip (36.8 KB) 3.Inse…

[Vishal_K]

@WadeShon

Refer my solution and do changes as below

1. You can modify my read range of particular sheet from A:L instead of A:M

“A”+(rowIndexOfGameStart+2).ToString+“:L”+(rowIndexOfGameEnd-1).ToString

I have used +2 to start from SLno row because your rowIndexOfGameStart will give the row index of GAME keyword.

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

3. Now you have fetched the Dt of the game then do write range starting cell with A7.

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

[WadeShon]

@Vishal_K hi, I tried to edit it but can’t seem to work

Are you able to implement it in the xaml file and I can go through from there to understand it? Thanks in advance!

[Vishal_K]

Hi @WadeShon

Refer to this XAML

WadeShon.zip (39.3 KB)

1. It will read your template and write the game data over it and copy to the output folder
2. Also, it will set password reading from Password template to each game excel files

Find the output screenshots below (It write ranges on your template the way it is formatted)