VBA中的结帐工作簿 [英] Checkout workbook in VBA

问题描述

我有一些VBA，我想在进行更改之前先检查一下工作簿. VBA代码位于其自己的工作簿中，并且由用户打开另一个工作簿并选择一个执行代码以修改工作簿的按钮来激活.首先，我要检查工作簿是否已签出.我遇到的问题是Workbooks.CanCheckOut(ActiveWorkbook.FullName)始终返回false，即使未检出工作簿.

I have some VBA i'm trying to get to check out a workbook before making changes. The VBA code sits in its own work book, and is activated by a user opening the other work book and selecting a button which executes code to modify the workbook. At the beginning I want to check if the work book is checked out. The issue I am getting is Workbooks.CanCheckOut(ActiveWorkbook.FullName) always returns false, even if the work book is not checked out.

If Workbooks.CanCheckOut(ActiveWorkbook.FullName) = True Then
    Workbooks.CheckOut (ActiveWorkbook.FullName)
    MsgBox "This workbook has been checked out"
    Process = True
ElseIf ActiveWorkbook.CanCheckIn = False Then 'if not checked out
    Process = False
    MsgBox ("The Document may not be checked out, Import Process is ending.")
Else
    Process = True
End If 'If CanCheckin = False

预先感谢您的帮助.

推荐答案

通过反复试验，我发现Workbooks.CanCheckOut (Filename:= FullName)其中FullName是SharePoint文件的URL 仅适用于未在其中打开的文件当前的Excel实例.

I've found through trial and error that Workbooks.CanCheckOut (Filename:= FullName) where FullName is the URL for the SharePoint file only works for files that are not open in the current instance of Excel.

如果您在Excel的当前实例中打开了文件，该方法将始终返回False.

The method will always return False if you have the file open in the current instance of Excel which is obviously the case here.

Workbooks.CheckOut (ActiveWorkbook.FullName)打开文件，将其检出，然后莫名其妙地关闭文件.因此，打开和检出SharePoint文件成为一个三步过程.

Workbooks.CheckOut (ActiveWorkbook.FullName) opens the file, checks it out and then inexplicably, closes the file. So opening and checking out a SharePoint file becomes a 3 step process.

Sub CheckOutAndOpen()
Dim TestFile As String

    TestFile = "http://spserver/document/Test.xlsb"
    If Workbooks.CanCheckOut(TestFile) = True Then
        Workbooks.CheckOut(TestFile)
        Workbooks.Open (TestFile)
    Else
        MsgBox TestFile & " can't be checked out at this time.", vbInformation
    End If
End Sub

这有点直观，因为当手动使用SharePoint文件时，您必须打开它们以查看是否可以检出它们，然后执行检出操作.

This is all a bit counter intuitive because when working manually with SharePoint files you have to open them to see if they can be checked out and then perform the check-out operation.

MSDN或Excel VBA都没有提到如果您在当前的Excel实例中打开文件，则Workbooks.CanCheckOut (Filename:= FullName)方法始终返回False.

Neither MSDN or Excel VBA help mention that the Workbooks.CanCheckOut (Filename:= FullName) method always returns False if you have the file open in the current instance of Excel.

这篇关于VBA中的结帐工作簿的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！