Excel VBA Workbook.ChangeFileAccess [英] Excel VBA Workbook.ChangeFileAccess

问题描述

使用VBA更改Excel工作簿上的文件访问模式时遇到麻烦.我正在使用Office2010.

I'm having trouble with changing the file access mode on Excel workbooks using VBA. I am using 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

我不明白为什么会这样.显然，完全有可能以只读方式打开工作簿，将其更改为可读写，然后继续使用该对象.为什么这种情况有所不同?我在MSDN上找不到此行为的消息.
http://msdn.microsoft.com/en-us/library/ff193344.aspx

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

是否可以在尝试操作时在文件访问模式之间进行切换?

Is it possible to change between file access modes as I am trying to do?

推荐答案

我认为您通过Excel更改读/写状态的方式遇到了一个有趣的问题.为了将工作簿从只读切换为读/写，Excel将关闭该工作簿，然后再次将其打开.在此过程中，您的对象引用变成了奇怪的损坏.如果您添加以下行:

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:

  Set w = Workbooks("example.xlsx")

在将FileFileAccess更改为xlReadWrite之后，它又重新振作起来，但这并不完全理想.

after you ChangeFileAccess to xlReadWrite then it perks up again, but it's not exactly ideal.

克里斯

这篇关于Excel VBA Workbook.ChangeFileAccess的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！