title: Check if workbook in SharePoint 365 is locked for editing
body: <p>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 </p>
  
  <pre><code>location = "https://mycompany.sharepoint.com/sites/ABC/LibraryA/Book1.xlsx"
  Set wbk = Workbooks.Open(location)
  </code></pre>
  
  <p>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.</p>
  
  <p>If the workbook were stored on my company file server, things would be easy. I could use this <a href="https://support.microsoft.com/en-us/kb/291295" rel="nofollow noreferrer">Macro code to check whether a file is already open</a> provided by the Microsoft support site: </p>
  
  <pre><code>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
  </code></pre>
  
  <p>The problem is that when I run this with the SharePoint URL, the error code returned is 75 (<a href="https://msdn.microsoft.com/en-us/library/aa264531%28v=vs.60%29.aspx?f=255&amp;MSPPError=-2147217396" rel="nofollow noreferrer">Path/File access error (Error 75)</a>, regardless of whether the file is in use or not.  </p>
  
  <p>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 <a href="https://support.microsoft.com/en-gb/kb/2616712" rel="nofollow noreferrer">How to configure and to troubleshoot mapped network drives that connect to SharePoint Online sites in Office 365</a>. Going through this would be too much for the end user.</p>
  
  <p>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.</p>
  
  <p>Ideas welcome. </p>
  
  <p><strong>Edit:</strong> Comments suggested approaches to open the workbook as read only, then change a workbook property, or open the workbook and evaluate the <code>.ReadOnly</code> property. The problem with this approach is that if the workbook is changed from Read Only to Edit, this dialog pops up:</p>
  
  <p><a href="https://i.stack.imgur.com/qbnu2.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/qbnu2.png" alt="enter image description here"></a> </p>
  
  <p>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. </p>