Excel VBA Workbook.ChangeFileAccess
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5030336/
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
Excel VBA Workbook.ChangeFileAccess
提问by Thomas Wilkins
I'm having trouble with changing the file access mode on Excel workbooks using VBA. I am using Office 2010.
我在使用 VBA 更改 Excel 工作簿上的文件访问模式时遇到问题。我正在使用 Office 2010。
I want to be able to alternate between read only and read write mode as appropriate. However it seems changing a workbook from read/write to read only and then back again as in the code below causes an automation error when trying to access any member of the workbook object.
我希望能够根据需要在只读和读写模式之间切换。但是，似乎将工作簿从读/写更改为只读，然后再次返回，如下面的代码所示，在尝试访问工作簿对象的任何成员时会导致自动化错误。
Public Sub example()
Dim w As Workbook
'open workbook with write access
Set w = Workbooks.Open("example.xlsx", ReadOnly:=False)
Debug.Print w.Name 'sucessfully accesses members of w
'change file access to read only
w.ChangeFileAccess XlFileAccess.xlReadOnly
Debug.Print w.Name 'successfully accesses members of w
'change file access back to read/write
w.ChangeFileAccess XlFileAccess.xlReadWrite
Debug.Print w.Name 'fails to access members of w with automation error
End Sub
I don't understand why this is the case. It is obviously perfectly possible to open a workbook as read only, change it to read write and then continue to use the object. Why is this situation different? I can find no mention of this behaviour on MSDN.
http://msdn.microsoft.com/en-us/library/ff193344.aspx
我不明白为什么会这样。显然完全可以以只读方式打开工作簿，将其更改为读写，然后继续使用该对象。为什么这种情况不同？我在 MSDN 上找不到这种行为的提及。
http://msdn.microsoft.com/en-us/library/ff193344.aspx
Is it possible to change between file access modes as I am trying to do?
是否可以像我尝试的那样在文件访问模式之间进行更改？
采纳答案by Chris Rae
I think you've hit an interesting foible with the way that Excel changes the read/write state. In order to switch a workbook from read-only to read/write, Excel closes that workbook and opens it again. During this process, your object reference turns into something strangely broken. If you add the line:
我认为您在 Excel 更改读/写状态的方式上遇到了一个有趣的弱点。为了将工作簿从只读切换为读/写，Excel 会关闭该工作簿并再次打开它。在这个过程中，你的对象引用变成了奇怪的东西。如果添加以下行：
Set w = Workbooks("example.xlsx")
after you ChangeFileAccess to xlReadWrite then it perks up again, but it's not exactly ideal.
在您将文件访问更改为 xlReadWrite 之后，它再次振作起来，但它并不完全理想。
Chris
克里斯
