| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to get username during workbooks.open macro
I have a macro that opens an xl2003 workbook on a network. If the workbook
being opened is already opened by another user, the macro will open a read only copy without displaying the usual "Locked fo editing" message. I figured out how to stop the file from opening, but I really need to be able to display the name of the user that has the file locked. I've been trying to figure this out for about 3 hours now. Any help would be greatly appreciated! Here is an example of the code I'm using to stop the file from opening, I'm sure there's a more efficient way to do it, but I'm kinda learning as I go. Sub testreadonly() Workbooks.Open ("M:\read only test.xls") If ActiveWorkbook.ReadOnly Then MsgBox ("This workbook is currently opened by another user. Please try again later.") ActiveWorkbook.Close End If End Sub |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
How to get username during workbooks.open macro
Just allocate some cell to hold the information. Then in a Workbook_Open
routine: 1. see if you are Unlocked (first user) 2. stuff the username in a cell 3. immediate Save The next user can look at the cell and see who has it open. -- Gary''s Student - gsnu200739 "Chuck W" wrote: I have a macro that opens an xl2003 workbook on a network. If the workbook being opened is already opened by another user, the macro will open a read only copy without displaying the usual "Locked fo editing" message. I figured out how to stop the file from opening, but I really need to be able to display the name of the user that has the file locked. I've been trying to figure this out for about 3 hours now. Any help would be greatly appreciated! Here is an example of the code I'm using to stop the file from opening, I'm sure there's a more efficient way to do it, but I'm kinda learning as I go. Sub testreadonly() Workbooks.Open ("M:\read only test.xls") If ActiveWorkbook.ReadOnly Then MsgBox ("This workbook is currently opened by another user. Please try again later.") ActiveWorkbook.Close End If End Sub |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
How to get username during workbooks.open macro
That should do it. Thanks a lot.
"Gary''s Student" wrote: Just allocate some cell to hold the information. Then in a Workbook_Open routine: 1. see if you are Unlocked (first user) 2. stuff the username in a cell 3. immediate Save The next user can look at the cell and see who has it open. -- Gary''s Student - gsnu200739 "Chuck W" wrote: I have a macro that opens an xl2003 workbook on a network. If the workbook being opened is already opened by another user, the macro will open a read only copy without displaying the usual "Locked fo editing" message. I figured out how to stop the file from opening, but I really need to be able to display the name of the user that has the file locked. I've been trying to figure this out for about 3 hours now. Any help would be greatly appreciated! Here is an example of the code I'm using to stop the file from opening, I'm sure there's a more efficient way to do it, but I'm kinda learning as I go. Sub testreadonly() Workbooks.Open ("M:\read only test.xls") If ActiveWorkbook.ReadOnly Then MsgBox ("This workbook is currently opened by another user. Please try again later.") ActiveWorkbook.Close End If End Sub |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
How to get username during workbooks.open macro
You are welcome. The easy part is saving the username. The trick is the
immediate save! -- Gary''s Student - gsnu200739 "Chuck W" wrote: That should do it. Thanks a lot. "Gary''s Student" wrote: Just allocate some cell to hold the information. Then in a Workbook_Open routine: 1. see if you are Unlocked (first user) 2. stuff the username in a cell 3. immediate Save The next user can look at the cell and see who has it open. -- Gary''s Student - gsnu200739 "Chuck W" wrote: I have a macro that opens an xl2003 workbook on a network. If the workbook being opened is already opened by another user, the macro will open a read only copy without displaying the usual "Locked fo editing" message. I figured out how to stop the file from opening, but I really need to be able to display the name of the user that has the file locked. I've been trying to figure this out for about 3 hours now. Any help would be greatly appreciated! Here is an example of the code I'm using to stop the file from opening, I'm sure there's a more efficient way to do it, but I'm kinda learning as I go. Sub testreadonly() Workbooks.Open ("M:\read only test.xls") If ActiveWorkbook.ReadOnly Then MsgBox ("This workbook is currently opened by another user. Please try again later.") ActiveWorkbook.Close End If End Sub |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
How to get username during workbooks.open macro
Look in xl VBA help at the writeReservedBy property of the workbook object.
-- Regards, Tom Ogilvy "Chuck W" wrote: I have a macro that opens an xl2003 workbook on a network. If the workbook being opened is already opened by another user, the macro will open a read only copy without displaying the usual "Locked fo editing" message. I figured out how to stop the file from opening, but I really need to be able to display the name of the user that has the file locked. I've been trying to figure this out for about 3 hours now. Any help would be greatly appreciated! Here is an example of the code I'm using to stop the file from opening, I'm sure there's a more efficient way to do it, but I'm kinda learning as I go. Sub testreadonly() Workbooks.Open ("M:\read only test.xls") If ActiveWorkbook.ReadOnly Then MsgBox ("This workbook is currently opened by another user. Please try again later.") ActiveWorkbook.Close End If End Sub |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
How to get username during workbooks.open macro
Man, this is exactly what I spent 3 hours looking for. You would think this
would have popped up somewhere in one of the 50 different searches I tried! Thanks. "Tom Ogilvy" wrote: Look in xl VBA help at the writeReservedBy property of the workbook object. -- Regards, Tom Ogilvy "Chuck W" wrote: I have a macro that opens an xl2003 workbook on a network. If the workbook being opened is already opened by another user, the macro will open a read only copy without displaying the usual "Locked fo editing" message. I figured out how to stop the file from opening, but I really need to be able to display the name of the user that has the file locked. I've been trying to figure this out for about 3 hours now. Any help would be greatly appreciated! Here is an example of the code I'm using to stop the file from opening, I'm sure there's a more efficient way to do it, but I'm kinda learning as I go. Sub testreadonly() Workbooks.Open ("M:\read only test.xls") If ActiveWorkbook.ReadOnly Then MsgBox ("This workbook is currently opened by another user. Please try again later.") ActiveWorkbook.Close End If End Sub |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Run Macro In All Open Workbooks | Excel Discussion (Misc queries) | |||
| Repeat macro for all open workbooks | Excel Programming | |||
| XMLHTTP.OPEN + PASSWORD + USERNAME | Excel Programming | |||
| Excel 2003 Workbooks.Open with CorruptLoad=xlRepairFile fails on Excel 5.0/95 file due to Chart, with Error 1004 Method 'Open' of object 'Workbooks' failed | Excel Programming | |||
| Open two new workbooks with macro | Excel Programming | |||
![Chuck W[_2_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Linear Mode
