CODEWITHSUNDEEP
vbaexcel
Jeanclaude
Jeanclaude

Reputation: 189

Excel VBA function to recognise Read-Only mode

I am using some code which opens another file to get data. This uses a function which I found somewhere called IsWorkBookOpen to check if the file is already open. This code below works well but I am trying to make it work in Read-Only mode.

What I would like to do is only open the file in Read Only mode. So update the Workbooks.Open FileName:="R:\Development\Copy of Product Information.xlsm", ReadOnly:=True, Password:="bcd"

I have tried updating this code to open the file in Read Only, but the macro doesn't recognise the file is already open ( in read only mode) and tries to open it again.

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

The IsWorkBookOpen function code:

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

I would like to know a way to modify the IsWorkBookOpen to handle Read-Only mode please.

Upvotes: 0

Views: 4011

Answers (3)

Brandon Barney
Brandon Barney

Reputation: 2392

Something like this will work (without needing additional references) and will tell you whether the workbook is open by the application, and whether or not the workbook is readonly. By default, the function will only return true if the workbook is open, and is read-only.

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

Upvotes: 0

ThunderFrame
ThunderFrame

Reputation: 9461

Try the following, which will tell you if a file is marked as read-only at the file system level, which is not the same as a file opened read-only by an application.

'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

Upvotes: 2

jkpieterse
jkpieterse

Reputation: 2956

If you are opening the workbook read-only I guess your function always returns false, unless some other process or user has the file open. If you just need to check if the workbook is open in the current Excel session, you could just use something like:

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

Upvotes: 1

Related Questions