检测 SharePoint 文件是否已打开 [英] Detect if SharePoint File is Open

问题描述

第一次在这里发帖，如果我不遵守任何准则，我深表歉意.

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

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

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!

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

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.

我不想使用 SharePoint 的签出功能并已将其禁用.我的团队不太勤奋地重新检查.

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.

非常感谢！

'**********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 小时后，我想出了一个快速而肮脏的解决方案.它不是最好的，但经过大量研究直到现在唯一合适的.这是我的代码:

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:

检测SharePoint文件是否被另一个用户打开，如果是打开文件，如果没有关闭它"

"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

这篇关于检测 SharePoint 文件是否已打开的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！