Excel automation with count logic

Help Activities
, , ,
1

Hi all,

Please guide me how I can achieve it.
Ex.,
Have folder ,inside folder have 20 excel,each excel may contain 15 sheets,

The requirement is need to extract the id of each sheets in a excel,but I’d should not exceed the count 150,once it reach 150 the process has to stop and add all IDs to queue.
Here the problem is even if the count reaches 150 ,if any sheets are pending in the open excel it has to finish the opened excel and then stop.how can I achieve this.

Guide me any solution or logic.

2

Hello Yeshashwini,

I am breaking the case into smaller pieces with which if you need further help, am happy to guide.

Step 1 : Calculate the number of files in the folder or use a for loop to iterate within the folder.

Step 2 : store the complete data from every sheet into a data table.

Step 3 : create an excel from UiPath that will have two sheets “To be added into queue” and “Added in queue”

After step 3 you would have consolidated all data from every excel and every sheet into single file single sheet.

This first 3 steps is required only when you run dispatcher for the first time to check if it is first create the step 3 excel file in the format “QueueItems_(Today’sdate)”. This you can check with path exists activity if it exists then it’s not running for first time if it doesn’t then it is running for first time.

Step 4 : Read the entire data from “To be added into queue” sheet. Initiate a counter variable which should be incremented till it reaches 150 or whatever the limit you want to have.

Step 5 : Once the number of items you want to be loaded into the queue is attained, place the 150 records in “Added to Queue” sheet and remove them from “To be added into queue” sheet.

You can trigger the dispatcher as many times required once performer completes the transactions loaded into queue.

If you have any doubts or need further help please mention the step and where you are having difficulty.

Hope this helps you.

Happy automating .!!

Thanks,
Gautham.