Detect if SharePoint File is Open

Question

First post here, I apologize if I'm off on any guidelines.

Here is my challenge: I have a status tracking file which is saved to SharePoint. Macros will open up this status tracker, record some info, save, and close the file. I am trying to include some code to detect if another user has that status file open, otherwise the macro will blow up when it sees it cannot save changes. I know it's not a very elegant system, but it will do for now!

The code below works in detecting if a file is open, but only for local files (saved to the C:\ drive for example). I can't get it to work for files saved to SharePoint. I know my SharePoint file path is correct, I can open the file using the "Workbooks.Open" operation. When I try to run the code for a SharePoint file, it always returns that the file is NOT open by another user, even if it is.

I'd prefer to not use the Checked Out feature of SharePoint and have disabled it. My team is not very diligent in checking things back in.

Thanks very much!

'**********Function to check if workbook is open**********
Function IsWorkBookOpen(strFileName As String)

    On Error Resume Next
    ' If the file is already opened by another process,
    ' and the specified type of access is not allowed,
    ' the Open operation fails and an error occurs.
    Open strFileName For Binary Access Read Write Lock Read Write As #1
    Close #1

    'If no error, file is not open.
    If Err.Number = 0 Then
        IsWorkBookOpen = False
        End If

    'Error #70 is another user has the file open in edit mode.
    If Err.Number = 70 Then
        IsWorkBookOpen = True
        End If

    'Error #75 is another user has the file open in read only mode.
    If Err.Number = 75 Then
        IsWorkBookOpen = False
        End If

End Function

'**********Running the actual code**********

Sub Button1_Click()

'Go into Status Sheet if it's not open. Otherwise skip it.
If IsWorkBookOpen("\\source.yadda.com\Top_Secret_File_Path\BCR Status Sheet.xlsm") Then
    MsgBox ("'BCR Status Sheet.xlsm' is open.")
    Else: MsgBox ("Open it up, do a bunch of stuff.")
End If

Workbooks.Open ("\\source.yadda.com\Top_Secret_File_Path\BCR Status Sheet.xlsm")

MsgBox ("Cruzin' along with rest of macro.")

End Sub

Answer 1

I had the same issue today and solved it like stated below.

The file on SharePoint: when someone opens this file, make it automatically open an other file on a location which you then can check if it is open.

code snipped from my main programme

'path to file on SharePoint
pathDatabank = "http://...sharepoint/sites/.... .xlsm" 

'path to my temp file (this file is opened by my file on SharePoint)
pathTempfile = "\\ location you can check the file\....\temp.xlsx"

'check if the temp file is open - with your function
If IsWorkBookOpen(pathTempfile) Then      
    ' Display a message stating the file is in use.
    MsgBox "Data bank is in use by an other user."        
    Exit Sub        
End If`

'before I close and save my file on SharePoint, I close the temp file
Workbooks("temp.xlsx").Close SaveChanges:=False

code in my file on SharePoint to open temp file - ThisWorkbook

Private Sub Workbook_Open()
  Workbooks.Open ("path to your temp file ... \temp.xlsx")
  ThisWorkbook.Activate
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
  On Error Resume Next 'because I closed the file in my main programme
  Workbooks("temp.xlsx").Close SaveChanges:=False
End Sub

Answer 2

after fighting with this problem over 8 hours at work I figured out a quick and dirty solution. It is not the best one, but after a lot of research till now the only suitable. Here's my code:

"detect if SharePoint file is opened by another user, if yes open file, if not close it"

Sub accessWorkbook()
    Dim url As String
    url = "mySharePointURL"

    MsgBox workbookOpen(url)
End Sub


Function workbookOpen(url As String) As Boolean
    'return false if file is not locked by another user
    workbookOpen = False


'open the workbook in read.only mode, so does no message is displyed when the file is use
Set wb = Workbooks.Open(url, False, True)
'change file access to ReadWrite without notifying if the file is locked by another user
On Error Resume Next
wb.ChangeFileAccess xlReadWrite, False, False

'if the file is locked, this will return "true"
workbookOpen = wb.ReadOnly

'if the file is locked, it wil lbe closed without no changes
If read.only = True Then
    wb.Close
End If

End Function