0

I have an Excel shared workbook stored on a legacy SharePoint 2010 document library, which needs to be opened for editing by multiple users via a VBA tool. To pre-empt the question: I am not sure whether the library enforces CheckOut before editing, but I am not the site owner and do not even have permissions to see this setting.

I have used the .CanCheckOut property to see whether another user already has the document checked out (by my tool, which always Checks Out before edit, even if the library does not enforce it), with the code aborting with a message to try again later, if necessary. There is a scenario though, where a user might go into the shared file and manually edit, then check back in but not close the file immediately. This means that the .CanCheckOut property is TRUE (the file is no longer checked out) but a 'File In Use' dialogue is triggered at runtime by another user trying to use my tool, because the shared file is still technically open in edit mode (by someone else), despite not being Checked Out.

Is there any way to check for this scenario i.e. where the shared file is Checked In but is open as writeable by another user? I would rather this avoid any user interaction, but even if we cannot avoid the 'File In Use' dialogue, I need a way to detect this and exit gracefully.

This post was woefully abandoned with the same issue: Check if workbook in SharePoint 365 is locked for editing

RobBaker
  • 137
  • 11

1 Answers1

1

Please, try the next function:

Function IsFileBlocked(strFileName As String) As Boolean
  On Error Resume Next
   Open strFileName For Binary Access Read Write Lock Read Write As #1
   Close #1
   IsFileBlocked = Err.Number
   Err.Clear: On Error GoTo 0
End Function

It can be used in this way:

If Not IsFileBlocked("\\pathToYourWorkbook\TestWorkbook.xlsx") Then
    Workbooks.Open "\\pathToYourWorkbook\TestWorkbook.xlsx"
Else
    MsgBox "File bloked (open by another user)...", vbInformation, "Open aborted"
End If

Instead of intranet path (I used for testing reasons), you have to use the path to your sharePoint workbook, of course... I do not use a sharePoint location and I did not test it in these circumstances.

The bases of the function is the fact that an open workbook cannot be opened For Binary Access. And this evaluation takes only some milliseconds (on intranet, at least...).

FaneDuru
  • 38,298
  • 4
  • 19
  • 27
  • I thought you had not read the original zombie post where a very similar method does not work, but I see you have used 'Binary Access' in the dummy file operation, and this does seem to work on my SharePoint configuration. Well done and thank you - this had been driving me a bit crazy! – RobBaker Jun 23 '20 at 14:02
  • @RobBaker: I must confess that I did not read the post you are referring to... I only posted a pice of code I used to use when needed for a similar purpose. But, like I said, only on intranet. Theoretically, it should work also in your case... Glad I could help! – FaneDuru Jun 23 '20 at 14:06