Skip to main content

I am trying to find a way for several bots to work from the same shared Excel file.  Assume there is a large list of data (Account Numbers) in one column and another column beside it called “RPA Status”.  When one bot starts to work an account, it looks at the Excel file to find the first Account Number with an empty RPA Status field.  It then copies this Account Number and writes “In Process” to the RPA Status field so that another bot will not pick up this Account Number.  It writes back “Complete” once it has completed whatever process it is doing.  Ideally, I’d like to have multiple machines pulling in and writing back to this Excel file.

 

The issue is that if I try running another bot pulling from the same file, the Excel is in “Read Only” status until the other workflow is completely finished.  Is there a way to write to an Excel file and immediately close out the connection so that another bot can write to it?  I know SQL is an option, but that raises several other issues.

@kwstroud 2 ways you can achieve the results you want.

  1. Use Excel Online - Put your Excel in a SharePoint Online site and have the bots interact with this file. Excel files on SharePoint Online are configured to allow multiple access at the same time. 
  2. Use a loop condition to check until the file is closed before continuing - In this scenario, you will need to perform a small exception handling at the point where you are writing to the file. When the file is in use, the Paste Excel command will return an error “FILE_READ_ONLY”. All you need is to catch the error and loop it until the error is gone to proceed. Do however take note that this may also lead to an infinite loop condition so you may want to add additional controls to prevent that from happening.

     

Thanks @Danny Toh - I’m creating new files with the workflow each day (based on the date) - Example:  FileName05172024.xlsx.  Can you directly create a new Excel file in SharePoint Online with Nintex RPA?  

 

The reason the 2nd option won’t work is that each bot will be continuously pulling from the Excel file - It seems to be locked until the flow completely ends.  It seems like this option would have the first bot that accesses the file complete all of the work and end before the Read Only error goes away, and by then it’s too late.

 

Thanks so much for the reply!!

 @kwstroud you can’t create an Excel directly on SharePoint Online. You can refer to the list of commands that interact with SharePoint Online here.

Hi, not sure if this issue is still relevant, but I solved a similar issue by doing the write to excel in Python using openpyxl Library. This allowed me to get more precise control on when the workbook is supposed to be closed.

Another option I’ve used is using a CSV (or txt file) as it doesn’t get locked when multiple robots are writing to it. Then when you need it in an excel format you can convert the latest version of the CSV.

Reply


View our Community Site Map

Nintex Community Sitemap
Terms & ConditionsCookie settings