1

We have just started using Filr as our file sharing platform, in short we have a seperate excel file for each of our shipments that we export and we have different users accessing these files although editing should never happen concurrently.

I have no formal IT background, I am an accountant and I know the basics. I have over the last year started teaching myself VBA using trial and errors from what I find on platforms like these so I apologise for any ignorance :)

The problem is that it seems Filr allows any excel files to be edited by multiple users, without any warning when opening the file and without the sharing capabilities being activated within excel. This is causing conflicting copies being created and confusion as to which is the correct file. I have tried to address this with the IT department (from our marketing company) managing Filr and am not getting any real assistance to address the problem.

So I decided to explore using VBA to warn or open as read only if a file is already open by another user and I have tried to no avail using similar posts to test if the file is open.

I have gotten to the point that it recognises that the current person opening the file, has the file open, which is pretty obvious. But get no further for it to recognise that another user already has the same file open.

Am I chasing a unicorn? Is this perhaps only controllable by Filr setup / settings? Or does anyone have any magical insight to help?

I hope I have posted my problem appropriately :)

Here is my code, so it detects me as 1 user, but when my colleague opens the file after me it still only detects the user count as 1 (not 2 for both of us):

Sub Workbook_Open()

Users = ActiveWorkbook.UserStatus

If UBound(Users) = 1 Then
MsgBox "File not in use"
Else
MsgBox "File already open"
End If

End Sub
Community
  • 1
  • 1
Antoinette
  • 11
  • 2
  • 1
    Can you share the code you are working with? That will make your question more on-topic for the site. You can check out this post as well: http://stackoverflow.com/questions/9373082/detect-whether-excel-workbook-is-already-open – Robin Mackenzie Mar 30 '17 at 08:12
  • Thanks for the feedback @RobinMackenzie , I have updated my post with the code and described the result. I have also tested the linked code for the function to check if the workbook is open, and it works. But even if I am the first user to open it picks up that the workbook is open, which it is by the first user opening it, but it won't detect if it is open by anyone else. The idea is to replace the read-only/notify warning which would be present in normal files, but it seeming like this is not going to be possible in the Filr space :( – Antoinette Mar 31 '17 at 05:21
  • Maybe have a look at this https://msdn.microsoft.com/en-us/library/office/aa223887(v=office.11).aspx. But you would have to make all the workbooks shared. – Robin Mackenzie Mar 31 '17 at 15:04
  • Thanks looks like a great idea, will do a test and let you know! – Antoinette Apr 02 '17 at 23:11

0 Answers0