Test if a Workbook is Open

[RyanH]

Is there a way to test if a workbook is open anywhere in the network? I
currently have a macro that tests if a workbook is open, but it only works if
it is open on the users computer.

I have a Source Workbook that transfers data to an Archive Workbook. I want
to make sure that someone isn't using the Archive Workbook before I transfer
the data. Is this possible?

Thanks in Advance!

 

[Bob Phillips]

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
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\volker2.xls") Then
Workbooks.Open "C:\MyTest\volker2.xls"
End If
End Sub

 

[Dave Peterson]

Microsoft shares a way to check to see if the file is open here:
http://support.microsoft.com?kbid=138621
Microsoft's IsFileOpen function.