0

I'm using the VBA macro below function to check whether a file is already open. However, it missed the case that the file is open as Read-only.

Could you guys please suggest what should be added to the 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
braX
  • 11,506
  • 5
  • 20
  • 33
Bùi Thu Thảo
  • 11
  • 1
  • 7
  • See @DickKusleika answer in [Detect whether Excel workbook is already open](https://stackoverflow.com/questions/9373082/detect-whether-excel-workbook-is-already-open) – Siddharth Rout May 22 '21 at 04:31

1 Answers1

0

A really good place for excel help is https://www.excelforum.com/

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
ErrNo = Err
On Error GoTo 0

If IsFileLocked(FileName) = True Then
'Do stuff - Report and exit if true, etc
End If
Close ff

Select Case ErrNo
Case 0:    IsWorkBookOpen = False
Case 70:   IsWorkBookOpen = True
Case Else: Error ErrNo
End Select
End Function

Function IsFileLocked(strFileName As String) As Boolean
  On Error Resume Next
  Open strFileName For Binary Access Read Write Lock Read Write As #1
  Close #1
  IsFileLocked = Err.Number
  Err.Clear
End Function

Answer from: https://www.mrexcel.com/board/threads/vba-to-tell-if-file-is-read-only.965985/

James
  • 123
  • 9