Excel workbook already open

AndyB

New Member
Hi all

Has anyone written a code stage that can check to see if an Excel workbook is open with another user within the business

We have recently received input files from within the business which they have left open in error and caused us to be unable to move the file from the folder once loaded into the queue

I was thinking if we could run a code stage which checks first if the file is already open it can output a flag to identify this and then if it is already open we will not attempt to load the file

Thanks
 

VJR

Well-Known Member
Hi AndyB,

What happens when you open a file that is already opened with another user?
Do you get the below message?
If so you can spy the popup with the Title as shown below and then proceed accordingly.

error.JPG


But if you prefer a Code stage then you can find the below VBA code from Microsoft (or maybe find a Vb.net code) which needs to be accommodated in the Blue Prism Code stage after verifying its possibilities.
https://support.microsoft.com/en-us/help/291295/macro-code-to-check-whether-a-file-is-already-open
 

AndyB

New Member
Hi VJ

Sorry for the delay I have been out of office so haven't been checking.
We don't get the issue when loading the file, it opens no problem but after this we tend to move the file to an archive folder. It is at this point we have the issue as it cannot be moved and therefore next time around the process will attempt to load the file into our queues again and this will continue in a loop until the file can be moved.

I was hoping to be able to write some simple code that would identify already open files and then not attempt to load them and instead notify our control room operators

Regards

Andy
 

VJR

Well-Known Member
It is at this point we have the issue as it cannot be moved and therefore next time around the process will attempt to load the file into our queues again
Hi AndyB,

Why don't you load the file into the queue only if the file is successfully moved?
 

AndyB

New Member
Hi VJ

In all honesty we have been using Blue Prism for a lot of years and some things such as the loading of files has never really been an issue so has not been changed for many years, before I even joined the team. It is only recently we seem to be getting stakeholders adding files to folders and them leaving them open on their desktops

We probably already have 200+ processes running and to amend it to your suggested way would work but would take a considerable amount of time so I thought amending the object stage with some code would have been easier. Just looking at ways to try and improve

We shouldn't really have to as the sender should just close the instance they have open but that doesn't always happen. I do appreciate your suggestion though for new developments moving forward though

Regards
Andy
 

VJR

Well-Known Member
Hi VJ

In all honesty we have been using Blue Prism for a lot of years and some things such as the loading of files has never really been an issue so has not been changed for many years, before I even joined the team. It is only recently we seem to be getting stakeholders adding files to folders and them leaving them open on their desktops

We probably already have 200+ processes running and to amend it to your suggested way would work but would take a considerable amount of time so I thought amending the object stage with some code would have been easier. Just looking at ways to try and improve

We shouldn't really have to as the sender should just close the instance they have open but that doesn't always happen. I do appreciate your suggestion though for new developments moving forward though

Regards
Andy
Okay AndyB, if you are having 200+ processes and would like to make any code changes to check if the file is already open then you could think of doing something like the below macro code does.
https://support.microsoft.com/en-us/help/291295/macro-code-to-check-whether-a-file-is-already-open
 

KR2004

New Member
I don't mean to necromance this thread, but was an answer ever found?
I followed VJR's link, but the code there is for VBA only, it doesn't work for Blue Prism. I've tried modifying the code to work with BP, but it seems BP doesn't recognize any of the Lock or Read As arguments, so it keeps failing. Thoughts?
 
Top