Identifying the user that has a workbook open?

[DS]

Morning all,

Was wondering if anyone has a way to do the following...

From a submission form, data is transferred to a destination data table, set
up also in Excel. Should the data table currently be open by another user,
the submit abandons.

The standard "Workbook.xls is locked for editing by user XXXXX" message with
the option to Read Only/Notify etc has been replaced with a message stating
that the submit cannot be completed because someone else has the wb open -
not a problem. The data table closes etc blah blah.

What I'm wondering is whether anyone is familiar with a method whereby the
user that has the wb open can be extracted and placed into a custom message
such as the one above - so that if a single user has the data table open for
a significant period, he/she can be identified and hit round the head....

The code used, in case this is helpful, is:

****************
Application.DisplayAlerts = False
Workbooks.Open DB, ignorereadonlyrecommended:=True, Notify:=False
Application.DisplayAlerts = True
Set DBT = ActiveWorkbook

If DBT.ReadOnly = True Then
DBT.Close SaveChanges:=False
ThisWorkbook.Activate
Sheet1.Select
StrMsg = "The database is currently in use by another user. Please try
again later."
MsgBox StrMsg, vbCritical, "Submission Cancelled"
Exit Sub
End If
****************

And effectively, I would like to add the network logon id of the user who
currently has the target wb open to the StrMsg - which would normally appear
in the "standard" message if DisplayAlerts <> False.

Any ideas?
Thanks!

 

[NickHK]

The Windows method is relatively involved:
http://vbnet.mvps.org/code/network/netfileenum.htm

But you could cheat and set a cell value to the name of the user that opened
the file in the WB_Open event. As you are opening the file anyway (and
testing the .ReadOnly property), you could read this cell and grab the
user's name.
You can get the username to insert into the cell with:
Debug.print Environ("Username")
Depending how involved you wish to get, you could then send a message to the
offender, possibly with the Messenger service if it is not disabled, having
resolved the machine to sent to.
Or use
environ("COMPUTERNAME")
to insert the computer name upon open as above with the username.

Having said that, does Application.UserStatus return anything useful in your
situation ?

NickHK

 

[DS]

The cheating method appeals to me Nick.... always prefer to do things the
easy way!

With the items involved, we're looking at a small team, so sending
notifications etc won't really be necessary. I was mainly just looking fo a
way whereby a user who was locked out from submitting for hours because
someone else had forgotten they had the table open could sort it out amongst
themselves...

Thanks for this, and looking forward to hearing about Speed Dating in Excel
2007!

Cheers
DS