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

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

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

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

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

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