Check if workbook in SharePoint 365 is locked for editing

Question

I have a document library in SharePoint 365 and I use a local Excel macro file to open workbooks in SharePoint online via VBA. This works fine with code like

location = "https://mycompany.sharepoint.com/sites/ABC/LibraryA/Book1.xlsx"
Set wbk = Workbooks.Open(location)

However, when users are editing the workbook in Excel Online, the workbook is locked / in use, and then the code runs into a prompt to save a local copy of the file. I want to avoid that and branch to code that handles things differently if the workbook is in use.

If the workbook were stored on my company file server, things would be easy. I could use this Macro code to check whether a file is already open provided by the Microsoft support site:

Sub TestFileOpened()

    ' Test to see if the file is open.
    If IsFileOpen("c:\Book2.xls") Then
        ' Display a message stating the file in use.
        MsgBox "File already in use!"
        '
        ' Add code here to handle case where file is open by another
        ' user.
        '
    Else
        ' Display a message stating the file is not in use.
        MsgBox "File not in use!"
        ' Open the file in Microsoft Excel.
        Workbooks.Open "c:\Book2.xls"
        '
        ' Add code here to handle case where file is NOT open by another
        ' user.
        '
    End If

End Sub

' This function checks to see if a file is open or not. If the file is
' already open, it returns True. If the file is not open, it returns
' False. Otherwise, a run-time error occurs because there is
' some other problem accessing the file.

Function IsFileOpen(filename As String)
    Dim filenum As Integer, errnum As Integer

    On Error Resume Next   ' Turn error checking off.
    filenum = FreeFile()   ' Get a free file number.
    ' Attempt to open the file and lock it.
    Open filename For Input Lock Read As #filenum
    Close filenum          ' Close the file.
    errnum = Err           ' Save the error number that occurred.
    On Error GoTo 0        ' Turn error checking back on.

    ' Check to see which error occurred.
    Select Case errnum

        ' No error occurred.
        ' File is NOT already open by another user.
        Case 0
         IsFileOpen = False

        ' Error number for "Permission Denied."
        ' File is already opened by another user.
        Case 70
            IsFileOpen = True

        ' Another error occurred.
        Case Else
            Error errnum
    End Select

End Function

The problem is that when I run this with the SharePoint URL, the error code returned is 75 (Path/File access error (Error 75), regardless of whether the file is in use or not.

Mapping the SharePoint 365 location as a network drive (WebDav) is not an option. There are simply too many things to maintain as described in How to configure and to troubleshoot mapped network drives that connect to SharePoint Online sites in Office 365. Going through this would be too much for the end user.

I need to find a way to check with VBA if a file stored in SharePoint online is open for use, while using the URL to access the file.

Ideas welcome.

Edit: Comments suggested approaches to open the workbook as read only, then change a workbook property, or open the workbook and evaluate the .ReadOnly property. The problem with this approach is that if the workbook is changed from Read Only to Edit, this dialog pops up:

Even if I click to open a read-only copy, the result is wrong. If I could suppress this dialog and instead evaluate the trigger that caused the dialog to appear, I could take it from there.

There is a difference between file access and HTTPS resource access via URI. Thats why your `IsFileOpen` will not work with a URI. But what happens if you simply check `wbk.ReadOnly` ? Should be `FALSE` if not locked and `TRUE` if locked. — Axel Richter, May 04 '16 at 06:39
Have you seen or tried this? [Sharepoint detect if file is open](http://stackoverflow.com/questions/25668098/detect-if-sharepoint-file-is-open) — Moosli, May 04 '16 at 06:49
@AxelRichter At what point in the code would I apply this? The `Workbooks.Open(location)` command will already prompt to open a copy if the file is in use. How would I check `wbk.ReadOnly` to intercept that prompt? — teylyn, May 04 '16 at 20:52
@Moosli, thanks, but that approach opens a dialog if the file is in use. I edited the question. — teylyn, May 05 '16 at 04:28
@teylyn: I had tested this with our SharePoint and the workbook is simply opened read only without any dialog if it is opened writable already by someone else. I am not a SharePoint expert. So I don't know whether this is any setting in SharePoint. You have tried to suppress the dialog using `Application.DisplayAlerts = False`? — Axel Richter, May 05 '16 at 06:15
@AxelRichter Are you on SharePoint Online? If so, could you post the exact code? DisplayAlerts does nothing to the dialog. I see it every time. — teylyn, May 05 '16 at 06:40
@teylyn: "Are you on SharePoint Online?" No, our company has it's own SharePoint server. The code was simply `Set wbk = Workbooks.Open(location) : MsgBox wbk.ReadOnly` where `location` was a SharePoint URI. Excel was Excel 2007. Result of the code was `False` or `True` dependent of if the workbook behind the URI was opened already or not. No dialog ever. — Axel Richter, May 05 '16 at 07:06
@AxelRichter Ah, thanks. It's different with SharePoint online. Probably because simultaneous multi-user edit is now supported in Excel online, but not with Excel desktop. — teylyn, May 05 '16 at 10:20
I wonder what triggered a downvote almost two years after the question was posted. There still is no satisfactory answer to the actual issue. Meanwhile, Excel on a 365 license and online storage can do co-authoring. But that does not solve the issue as posed in the original question. So what is the downvote about? I guess the downvoter does not really understand the issues at play here. — teylyn, Feb 13 '18 at 10:12

score 0 · Answer 1 · answered Jul 07 '20 at 21:51

I also work with SharePoint & Office 365. The code below worked for me. In it, pickListFilePath is the path to the file on SharePoint 365.

'Open the workbook.  If it is already open (opens as read only),close it and loop to retry until it opens as editable.

TryOpenUntilOpenToEdit:
        Dim objExcel : objExcel = CreateObject("Excel.Application")
        objExcel.DisplayAlerts = False
        objExcel.ScreenUpdating = False

        On Error Resume Next
        Dim objPickListWB : objPickListWB = objExcel.Workbooks.Open(pickListFilePath, Notify:=False)

        If Err().Number <> 0 Or objPickListWB.ReadOnly Then
            objPickListWB.Close
            objExcel.Quit
            Err.Clear()
            GoTo TryOpenUntilOpenToEdit
        End If

Check if workbook in SharePoint 365 is locked for editing

1 Answers1

Linked