Can one excel file be updated by multiple bots at same time?

[AbhijitPatil]

Hi,
In a process I want to update an excel file.
So if queue items are more and multiple bots running same process.
then one excel file can be updated by multiple by bots at same time?
If not then what is the solution for this? 
Thanks,

------------------------------
Abhijit Patil
------------------------------

[RamónRequena_L1]

Hi Abhijit,

although it is technically possible (if the file is on a sharepoint or onedrive) I would highly advise against it. Make use of the Action "Is Read Only" and combine it with wait stages. 



Even if the file is in a Sharepoint, I would rather download it, make the edits and upload it again. You could otherwise incurr in Data Loss/overwriting. 

Perhaps there is a connector on the DX to avoid this workaround, in which case I would wait for others to chip in and share their 2 cents.

------------------------------
Ramón Requena López
RPA Developer
Magenta Telekom
------------------------------

[DurgaPedagopu]

You can setup locks and practically update the file from multiple bots. Not at the time though.!
the remaining bots can only access the file once the lock is released. However this may be a bad idea because it defeats the task parallelism of the process. You have to design the solution to make sure only one bot access the file to update, by keeping the data in another intermediate queue.

------------------------------
Durga Pedagopu
Principal Engineer
Invesco
Asia/Kolkata
------------------------------

[diane.sanzone]

Hi Abhijit,

I want to preface this with: I've never tried this myself, so my response is based on my knowledge of Excel more than experience with the BP Excel VBO.  here's hoping my logic holds true!

This seems like it should be possible, assuming you set the workbook correctly in Excel.  You can have multiple persons editing the same file at the same time, so why not multiple bots?  It depends on your Excel version how you do this.  For older versions of  Excel, if you save the excel file as a "shared workbook", it allows multiple users to access simultaneously and not get a "read only" error at file open.  There are some restrictions in Excel for shared workbooks that you should review first.  For Excel 365, I believe it's called "co-authoring". You can google either of these to get all sorts of information, but this MS site might be a good place to start:

https://support.microsoft.com/en-us/office/about-the-shared-workbook-feature-49b833c0-873b-48d8-8bf2-c1c59a628534

Once you have the workbook set to allow multiple user simultaneous access, the bots should be able to do their thing.  You'll want to have checks in place regarding updating the same field with multiple bots (make sure that doesn't happen), and you might need to create a custom object to interact with any pop up messages that appear from Excel on file save/close (you'll get warnings or messages about another user having modified the workbook while you had it open and how to handle the changes), but if you can get past that, I don't see why it wouldn't work.

I hope this helps.  I recommend playing around with the shared workbook/co-authoring feature in Excel manually with a colleague outside BP to understand the intricacies, then try to apply BP logic to "solve the problems" that arise.

Good luck!

------------------------------
Diane Sanzone
------------------------------

[Kishore_KumarDe]

Hi Abhijeet,

In blueprism, you have a concept of Environment lock(as Durga mentioned earlier) to handle that. But to have an efficient solution you can provide some more details of the use case. For example, Are you updating just the status in Excel? Is it possible to process all the queue items first and let one separate process read all the queue items and update the final excel only once? That way you don't have to access excel again and again. You can keep all the required information that you need to update in Queue items. 
Just FYI, In multi-bot architecture you should process everything from queue and try to avoid an shared resource which needs write access between queue items.

------------------------------
Kishore Deka
Lead Software Engineer
EPAM systems
------------------------------

[stepher]

Anhijit,
Honestly, I think most of your problems will come from the Excel functionality and not the Bue Prism VBO.  That is, from experience, having an MS Excell workbook that allowed multiple users access at the same time was challenging.  If one user saved their changes, changes actively being made by other uses were often not saved.  We also had issues where there would be 'phantom' instances of users remaining attached to the document long after they closed out of the file.  Currently, we use a Sharepoint version of a status log, which more or less mimics this funtion, but I still kind the need to check the workbook out a bit clunky.

As is mentioned by others, I think your best option is to use one or more workqueues to accomplish your stated goal.
1. Move the Excel data to a workqueue
   a. Bring the Excel document into one or more collections
      a1. If the current presentation order is important, then I would add index fields/columns, as necessary
   b. Move the collection into a workqueue
2. One or more of your Bots/Resources can process the work queue
3. Save the updated workqueue information to the same or a new Excel workbook
   a. Bring your workqueue back into a collection
   b. Save your collection to Excel
      b1.  I often will save the updated information to a new worksheet ('raw data' versus 'output') which allows for relatively easy audits by the Business.

Good luck,
Red

------------------------------
Robert "Red" Stephens
Application Developer, RPA
Sutter Health
Sacramento, CA
------------------------------