How to write data into excel in parallel

excel
activities
macro

#1

Hi, I want to write data into excel file n parallel way. To color 500 cells in excel, robot is taking 400 seconds. If I try to use parallel activity then it is error out saying file is already in use. This is sense since file is using by one of the parallel activity. I kept both the write range activities inside the excel scope but still same issue is coming.
To make coloring faster, please let me know is there any another to do? Thanks.


#2

Hi @suripaleru,

Would you be able to share your .xaml file and your excel file, so I can analyze it better?


#3

Hi @suripaleru,

One thing that you might do is to write the formula only on the first cell and then use excel shortcuts to replicate the same formula on the remaining cells. I tried to use the Write Cell activity inside the Parallel For Each With Body Factory but it doesn’t work well, some of the rows were inserted correctly but some were not. Therefore I don’t believe it’s possible to follow this strategy.

You may try to follow the example that I have created on the thread below:


#4

Are you using debug or run ?


#5

Just from my experience, running activities in Parallel doesn’t actually make your process faster. It will run one activity at a time regardless. So in order to speed up your process you will probably want to explorer more quicker ways to automate it.

Maybe creating a .vbscript file or executing a macro would be faster. If you select to use UiPath, highlighting the 500 cells then changing Formatting will be faster with UI automation in Excel using clicks and hotkeys rather than a For Each loop.

That’s what I’m thinking anyway.


#6

I’m using run


#7

use macros.if u use the parllel activity it can throw because same process doesnt accept to run two different threads.


#8

Could you please provide any sample example to do the same? Thanks.


#9

Attached sample flow. Kindly provide better solution. Thanks.

list.xaml (8.9 KB)


#10

Hello,

So I would recommend trying to avoid the For loop.
What you can do is set the Range as 1 string, like “A:A,D:D” or “2:2,4:4” or “A1:E100”. You can use comma between ranges. After you set the range, then just use that in your Set Color activity so they are all colored at once. Image below:

Then if you want to set the color based on values in the spreadsheet, you will need to find out the row and column number where the value is and you can convert the column to a letter for the range. There are a few efficient ways to do this without having to loop through every row. Unfortunately, I’m not that experienced with it and would need to research it which I don’t have time, lol.

Either that or you can have a template spreadsheet file that already has Conditional Formatting set (which is what I have been doing).

Thanks.


#11
  1. Create a Macro enabled Template in Excel
  2. In the template go to Options-> Save-> Default File Location and specify a custom location which UIPath can access.
  3. Create macros to set cell color and do any formatting you want.
  4. Save the file with the name Template.xltm.
  5. In the UIPath workflow open Template.xltm, perform the write operations you want and then call the macros to do the formatting.
  6. When the Excel activity scope closes it will save the file with the name Template1.xlsx in the location you specified in step 2.
  7. Rename and Move the file as per your need.