Determining if workbook is already opened by someone else?

  • Thread starter Thread starter Don Wiss
  • Start date Start date
D

Don Wiss

I have a project to open a few hundred workbooks, run a macro that makes
changes, and then save them. It is possible that a few of the workbooks may
be in use by a user. So the opening of the workbook will pop up a message
box asking if one wants to open read only. These I don't want to open, but
instead log them so I can go back later and process them. I don't want to
have someone monitor the running of the macro. I'd like the macro to be
able to determine if read only and to not open it. I would think there is a
command I could call prior to the open that returns the file's status.

Thanks, Don <www.donwiss.com> (e-mail link at home page bottom).
 
Hi Don,

Here is a simple function, and a test macro

Function IsFileOpen(FileName As String)
Dim iFilenum As Long
Dim iErr As Long

On Error Resume Next
iFilenum = FreeFile()
Open FileName For Input Lock Read As #iFilenum
Close iFilenum
iErr = Err.Number
On Error GoTo 0

Select Case iErr
Case 0: IsFileOpen = False
Case 70: IsFileOpen = True
Case Else: Error iErr
End Select

End Function


Sub test()
If Not IsFileOpen("C:\MyTest\test-1.xls") Then
Workbooks.Open "C:\MyTest\test-1.xls"
End If
End Sub
 
Back
Top