vba VBA中的结帐工作簿
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/22330499/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Checkout workbook in VBA
提问by LRS
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.
我有一些 VBA，我正在尝试在进行更改之前查看工作簿。VBA 代码位于其自己的工作簿中，由用户打开其他工作簿并选择执行代码以修改工作簿的按钮激活。一开始我想检查工作簿是否已签出。我遇到的问题是 Workbooks.CanCheckOut(ActiveWorkbook.FullName) 总是返回 false，即使工作簿没有被签出。
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
Thank you in advance for your help.
预先感谢您的帮助。
回答by Mark Fitzgerald
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.
我通过反复试验发现，Workbooks.CanCheckOut (Filename:= FullName)其中 FullName 是 SharePoint 文件的 URL仅适用于未在当前 Excel 实例中打开的文件。
The method will always return Falseif you have the file open in the current instance of Excel which is obviously the case here.
False如果您在 Excel 的当前实例中打开了文件，则该方法将始终返回，这里显然就是这种情况。
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.
Workbooks.CheckOut (ActiveWorkbook.FullName)打开文件，检查它，然后莫名其妙地关闭文件。因此，打开和检出 SharePoint 文件变成了一个 3 步过程。
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
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.
这有点违反直觉，因为在手动处理 SharePoint 文件时，您必须打开它们以查看它们是否可以签出，然后执行签出操作。
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.
MSDN 或 Excel VBA 帮助都没有提到Workbooks.CanCheckOut (Filename:= FullName)如果您在 Excel 的当前实例中打开文件，该方法总是返回 False。
回答by marionffavp
Sub CheckOutAndOpenFromSharePoint()
Dim FilePath As String
Dim FileName as string
FilePath = "http://Sharepoint site/Folder Name On SharePoint Site/"
FileName = "Testing.xlsx"
If Workbooks.CanCheckOut(FilePath & FileName) = True Then
Workbooks.CheckOut "http://Sharepoint%20site/Folder%20Name%20On%20SharePoint%Site/Testing.xlsx"
Workbooks.Open Filename:=FilePath & FileName
Else
MsgBox FileName & " can't be checked out at this time.", vbInformation
End If
End Sub
