Determining if workbook is already opened by someone else?
 

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).

Determining if workbook is already opened by someone else?
 

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

--
HTH

Bob Phillips

"Don Wiss" wrote in message
...
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).