How to get username during workbooks.open macro

[Chuck W]

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

 

[Gary''s Student]

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.

 

[Chuck W]

That should do it. Thanks a lot.

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]

You are welcome. The easy part is saving the username. The trick is the
immediate save!

 

[Tom Ogilvy]

Look in xl VBA help at the writeReservedBy property of the workbook object.

 

[Chuck W]

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.