Determine if a shared workbook is open

[Mark Lincoln]

Is there a way to determine if a shared workbook on a (Novell NetWare)
network drive is in use? I need to be able to replace a locked-down
shared workbook (for viewing by Those Who Should Not Make Changes)
with an updated copy produced from a non-shared Master file. I use a
separate workbook (I have my reasons) to open the Master file, save it
as an intermediate file, lock all its cells, protect all its sheets,
and save the resulting file in place of the old shared workbook.
Trying to save the file when someone has the old shared workbook open
will actually work, but not without some problems. So I don't want
the update code to run if either file is open.

I found the following code on Microsoft's Web site. It properly
determines if the Master workbook is open, but using it to test the
shared file produces a False result (i.e., file is not open) even if
the file is in use.

'-------------------
Function IsFileOpen(filename As String)
Dim filenum As Integer, errnum As Integer

On Error Resume Next ' Turn error checking off.
filenum = FreeFile() ' Get a free file number.
' Attempt to open the file and lock it.
Open filename For Input Lock Read As #filenum
Close filenum ' Close the file.
errnum = Err ' Save the error number that occurred.
On Error GoTo 0 ' Turn error checking back on.

' Check to see which error occurred.
Select Case errnum

' No error occurred.
' File is NOT already open by another user.
Case 0
IsFileOpen = False

' Error number for "Permission Denied."
' File is already opened by another user.
Case 70
IsFileOpen = True

' Another error occurred.
Case Else
Error errnum
End Select

End Function
'-------------------

Is there some other method I can use to find out if the shared
workbook is open? I would prefer not to have to actually open it via
code, but I'm willing to do so if there's no better alternative.

Mark Lincoln

 

[Mark Lincoln]

Based on some code from Microsoft, I've come up with a method that
works well enough:

'-------------------------------
Dim Users As Variant
Dim HowMany As Integer

Sub HowManyUsers()

Workbooks.Open ("F:\Foo\LookupFile.xls")

Users = ActiveWorkbook.UserStatus

HowMany = UBound(Users, 1)

MsgBox "Users: " & CStr(HowMany)

End Sub
'-------------------------------

If HowMany = 1, then only one person (i.e., the user running the Sub)
has the file open. If that's the case, I can then have my code close
the file and get on with the updating.

I still wish there was a way to do this without opening the file, but
even the Novell server never shows the Lookup file as being in use
except for a moment when it's first opened.

Mark Lincoln