Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Identifying the user that has a workbook open?
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! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Identifying the user that has a workbook open?
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" wrote in message ... 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! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Identifying the user that has a workbook open?
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 "NickHK" wrote: 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" wrote in message ... 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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Open workbook in user-defined folder | Excel Programming | |||
Identifying an open workbook / sheet | Excel Programming | |||
How to create a URL and when user clicks it to open a Excel workbook | Excel Programming | |||
Check For Open Workbook by another User | Excel Programming | |||
Excel not warning if another user has the workbook open | Excel Programming |