Excel VBA函数以识别仅读取模式

Question

我正在使用一些代码，该代码打开另一个文件以获取数据。这使用了我在某个地方发现IsWorkBookOpen的函数来检查文件是否已经打开。下面的该代码运行良好，但我试图使其在仅阅读模式下工作。

我想做的只是仅在读取模式下打开文件。因此，更新 Workbooks.Open FileName:="R:DevelopmentCopy of Product Information.xlsm", ReadOnly:=True, Password:="bcd"

我尝试更新此代码以仅在read中打开文件，但是宏未识别文件已经打开(仅在读取模式下(并尝试再次打开它。

Ret = IsWorkBookOpen("R:DevelopmentCopy of Product Information.xlsm")
If Ret = True Then
            Workbooks("Copy of Product Information.xlsm").Activate
            Sheets("Main").Select
Else
        Workbooks.Open FileName:="R:DevelopmentCopy of Product Information.xlsm", Password:="bcd"
        Sheets("Main").Select
End If

IsWorkBookOpen功能代码：

Function IsWorkBookOpen(FileName As String)
    Dim ff As Long, ErrNo As Long
    On Error Resume Next
    ff = FreeFile()
    Open FileName For Input Lock Read As #ff
    Close ff
    ErrNo = Err
    On Error GoTo 0
    Select Case ErrNo
    Case 0:    IsWorkBookOpen = False
    Case 70:   IsWorkBookOpen = True
    Case Else: Error ErrNo
    End Select
End Function

我想知道一种修改IsWorkBookOpen以处理仅阅读模式的方法。

Answer 1

尝试以下内容，它将告诉您文件是否在文件系统级别上标记为仅读取的文件，该文件与应用程序单独打开的文件不同。

'Add a reference to Microsoft Scripting Runtime
Function FileIsReadOnly(filePath As String) As Boolean
  Dim fso As Scripting.FileSystemObject
  Set fso = New Scripting.FileSystemObject
  Dim fil As Scripting.File
  Set fil = fso.GetFile(filePath)
  FileIsReadOnly = fil.Attributes And ReadOnly
End Function

Answer 2

如果您要打开"仅阅读"工作簿，我想您的功能总是返回false，除非某些其他过程或用户可以打开文件。如果您只需要检查当前Excel会话中的工作簿是否打开，则可以使用以下内容：

Function IsWorkbookOpen(sWbName As String) As Boolean
    Dim oWb As Workbook
    On Error Resume Next
    Set oWb = Workbooks(sWbName)
    IsWorkbookOpen = (Err.Number = 0)
End Function

Answer 3

这样的事情将有效(无需其他参考(，并会告诉您是否通过应用程序打开工作簿，以及工作簿是否已阅读。默认情况下，该函数只有在打开工作簿时才会返回true，并且仅阅读。

Function IsWorkBookOpen(ByVal FileName As String) As Boolean
    Dim TargetWorkbook As Workbook
    Dim IteratorWorkbook As Workbook
    For Each IteratorWorkbook In Application.Workbooks
        If IteratorWorkbook.FullName = FileName Then 
            Set TargetWorkbook = IteratorWorkbook
        End If
    Next
    If Not TargetWorkbook Is Nothing Then
        If TargetWorkbook.ReadOnly Then
            IsWorkBookOpen = True
            Exit Function
        End If
    End If
End Function