checking to see if a "shared" workbook is already open

M

mark kubicki

I want to check if a SHARED workbook is open (on the user's machine), so
that I can run code to open it if it is not (otherwise I would get a message
about opening an already open ... ) the workbook is HIDDEN to the user, so the
message would be both inappropriate and confusing...

in the past I've used this procedure, and it worked fine, but the workbook was NOT SHARED
currently i'm getting a errornum 0 (indicating the file is not open) even though it is open (on the user's machine):

Function IsFileOpen(filename As String)
Dim filenum As Integer, errnum As Integer
On Error Resume Next
filenum = FreeFile()
Open filename For Input Lock Read As #filenum
Close filenum
errnum = Err
On Error GoTo 0
Select Case errnum
Case 0
IsFileOpen = False
Case 70
IsFileOpen = True
Case Else
Error errnum
End Select
End Function

it was suggested that I check for the workbooks status, but that doesn't
tell me if it is "open" on the user's machine (only if is shared or
exclusive...)

Dim vMode
On Error Resume Next
If Me.ReadOnly Then
vMode = 3
Else
vMode = Application.VLookup(Application.UserName, Me.UserStatus, 3)
End If
MsgBox "Mode: " & Choose(vMode, "exclusive", "shared", "readonly")

thanks in advance,
mark
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top