检查SharePoint 365中的工作簿是否被锁定以进行编辑 [英] Check if workbook in SharePoint 365 is locked for editing

问题描述

我在SharePoint 365中有一个文档库，并且我使用本地Excel宏文件通过VBA在线打开SharePoint中的工作簿.可以与

之类的代码配合使用

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

但是，当用户在Excel Online中编辑工作簿时，该工作簿被锁定/正在使用中，然后代码运行到提示中以保存文件的本地副本.我想避免这种情况，如果工作簿正在使用中，请跳转到处理不同内容的代码.

如果工作簿存储在我的公司文件服务器上，事情将会很容易.我可以使用此

即使我单击以打开只读副本，结果也是错误的.如果我可以禁止显示该对话框，而是评估导致该对话框出现的触发器，则可以从那里获取它.

解决方案

我还使用SharePoint&Office 365.下面的代码为我工作.在其中，pickListFilePath是SharePoint 365上文件的路径.

 '打开工作簿.如果已经打开(以只读方式打开)，请将其关闭并循环重试，直到以可编辑方式打开为止.TryOpenUntilOpenToEdit:昏暗的objExcel:objExcel = CreateObject("Excel.Application")objExcel.DisplayAlerts = FalseobjExcel.ScreenUpdating = False关于错误继续昏暗的objPickListWB:objPickListWB = objExcel.Workbooks.Open(pickListFilePath，通知:= False)如果Err().Number<>0或objPickListWB.ReadOnly然后objPickListWB.CloseobjExcel.QuitErr.Clear()转到TryOpenUntilOpenToEdit万一 

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.

解决方案

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

这篇关于检查SharePoint 365中的工作簿是否被锁定以进行编辑的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！