检测 SharePoint 文件是否打开

第一次在这里发帖，如果我偏离了任何指导方针，我深表歉意。

这是我的挑战：我有一个保存到 SharePoint 的状态跟踪文件。宏将打开此状态跟踪器，记录一些信息，保存并关闭文件。我试图包含一些代码来检测另一个用户是否打开了该状态文件，否​​则当宏看到无法保存更改时将会崩溃。我知道这不是一个非常优雅的系统，但现在就可以了！

下面的代码用于检测文件是否打开，但仅限于本地文件（例如保存到 C:\ 驱动器）。我无法让它对保存到 SharePoint 的文件起作用。我知道我的 SharePoint 文件路径是正确的，我可以使用“Workbooks.Open”操作打开该文件。当我尝试运行 SharePoint 文件的代码时，它总是返回该文件未被其他用户打开，即使它是。

我不想使用 SharePoint 的“签出”功能并已将其禁用。我的团队不太勤于检查事情。

非常感谢！

'**********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

在工作中与这个问题斗争了 8 个多小时后，我找到了一个快速但肮脏的解决方案。它不是最好的，但经过大量研究，到目前为止是唯一合适的。这是我的代码：

“检测 SharePoint 文件是否被其他用户打开，如果是则打开文件，如果不是则关闭它”

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