determine if a file is open - modified

[alfredjp]

it is quite easy using vba to determine if another file is open, and do nothing if it is...

however i have a modified view of the problem...

UserA opens and excel file located in a common folder (network). what i would like to happen is if another UserB opens the same file (which of course, the "file already in use" message appears), that excel would close itself (only that particular workbook, though)...

any suggestions? ive tried lots of techniques already... but they all fail when it comes to UserA and UserB opening the same file within seconds of each other...

hhhmmm... btw, is there a way (maybe through calling the windows api) to trap the "file already in use" message, then go from there?

thanks a lot...

=alfred

 

[aMember]

I am using Excel '97 and this works for me.

Sub MyTest()
Application.DisplayAlerts = False
filenameandpath = PathToYourFile
On Error GoTo FileAlreadyOpen
Workbooks.Open _
FileName:=filenameandpath, _
updatelinks:=False, _
ReadOnly:=False, _
notify:=False
Application.DisplayAlerts = True
'if not already open, put your code here
Exit Sub

FileAlreadyOpen:
'to get here you know file is already open
'but it wasn't opened. Put code here on what
'to do if file didn't open
MsgBox "Determined that file " & filenameandpath & vbCrLf _
& "was already open. Can't edit an open file. Try again later."
Application.DisplayAlerts = True
End Sub

 

[alfredjp]

aMember, thanks for the prompt reply... unfortunately it doesnt solve the problem... maybe it is my fault for not explaining it succintly...

what i would like to do is write a code in an excel file's auto open event (auto_open, workbook_open, etc.) so that when it is opened (from a condition that is still closed) it would NOT do anything... BUT when it is opened (from a condition that it is already in use by another user) by another user, the workbook would automatically close itself...

* im trying to devise a solution for a workbook to check if it ITSELF has been opened more than once <--- maybe this would be better a statement of the problem... (^^)

=alfred

 

[rudo]

Hello Alfred,

See also a msg of Ratman to a similar question on July 3rd.

Greetings,

Rudo

 

[alfredjp]

btw, i may not be sure... but are you referring to the post pertaining to the url below:

thread707-69048

 

[alfredjp]

one more thing... just got an insight on the problem...

would using ActiveWorkbook.ReadOnly be the easier and more elegant solution?

if ActiveWorkbook.ReadOnly were FALSE, then it would mean that it IS the first user... if it were TRUE, then at least one user would then be accessing the file... chaining this into the Workbook_Open event, I could use a ActiveWorkbook.Close...

well, however, this method pre-requires that the file is not &quot;common-use&quot; based...

could anyone verify this?

thanks...