I've written VBA code to open a shared Workbook and make changes. I need my
code to determine whether another user has the Workbook open and whether, as
a consequence, my code is opening a read-only copy of the Workbook - in
which case, the code needs to abandon making changes, with advice to the
user to try again later.

Can I programmatically attempt to open a workbook in exclusive mode and then
see if the Workbook opened? Or is there a property I can examine after the
Workbook has opened to determine whether it's in read-only mode?

TIA
Geoff.

I would run this one from Ozgrid first and have it return a TRUE or FALSE to
decide on the rest of your code...


http://www.ozgrid.com/VBA/IsWorkbookOpen.htm#ONE




"GeoffG" wrote in message
...
I've written VBA code to open a shared Workbook and make changes. I need
my code to determine whether another user has the Workbook open and
whether, as a consequence, my code is opening a read-only copy of the
Workbook - in which case, the code needs to abandon making changes, with
advice to the user to try again later.

Can I programmatically attempt to open a workbook in exclusive mode and
then see if the Workbook opened? Or is there a property I can examine
after the Workbook has opened to determine whether it's in read-only mode?

TIA
Geoff.

Here is another idea from http://www.mvps.org/dmcritchie/excel/readonly.htm


Who Has the Read Only File (#reserved)
Macro supplied by Harald Staff, Programming, 2002-10-14.
Sub test()
Dim wbk As Workbook
Set wbk = Workbooks.Open("C:\temp\book1.xls")
If wbk.ReadOnly Then
MsgBox "Write reserved by " & _
wbk.WriteReservedBy
End If
End Sub













"GeoffG" wrote in message
...
I've written VBA code to open a shared Workbook and make changes. I need
my code to determine whether another user has the Workbook open and
whether, as a consequence, my code is opening a read-only copy of the
Workbook - in which case, the code needs to abandon making changes, with
advice to the user to try again later.

Can I programmatically attempt to open a workbook in exclusive mode and
then see if the Workbook opened? Or is there a property I can examine
after the Workbook has opened to determine whether it's in read-only mode?

TIA
Geoff.

Hi Mark:

Many thanks for your reply. The Harald Staff code seemed promising but
result was unexpected.

VBA code running in Workbook1 opens Workbook2, with an object variable
"objWBK2" pointing to Workbook2.

As expected, if no one else on the network has Workbook2 open, then:

objWBK2.ReadOnly = False

And, if someone else does have Workbook2 open, then:

objWBK2.ReadOnly = True.

Also, if someone else has Workbook2 open, Excel suspends the VBA program to
display a dialog telling me the name of the user who has Workbook2 open. On
clicking the "Read Only" button, my VBA program continues to open Workbook2.

But the next bit was unexpected. If the ReadOnly property is True (ie
someone else has Workbook2 open), I would have expected:

objWBK2.WriteReservedBy

to return the name of the user who has Workbook2 open. But in fact, it
returns my name, ie the name of the user who has Workbook1 open, ie where
the code is running. (Notice, the above line specifically asks for the
WriteReserveBy property of Workbook2.)

Any thoughts?

TIA
Geoff


"Mark Ivey" wrote in message
...
Here is another idea from
http://www.mvps.org/dmcritchie/excel/readonly.htm


Who Has the Read Only File (#reserved)
Macro supplied by Harald Staff, Programming, 2002-10-14.
Sub test()
Dim wbk As Workbook
Set wbk = Workbooks.Open("C:\temp\book1.xls")
If wbk.ReadOnly Then
MsgBox "Write reserved by " & _
wbk.WriteReservedBy
End If
End Sub













"GeoffG" wrote in message
...
I've written VBA code to open a shared Workbook and make changes. I need
my code to determine whether another user has the Workbook open and
whether, as a consequence, my code is opening a read-only copy of the
Workbook - in which case, the code needs to abandon making changes, with
advice to the user to try again later.

Can I programmatically attempt to open a workbook in exclusive mode and
then see if the Workbook opened? Or is there a property I can examine
after the Workbook has opened to determine whether it's in read-only
mode?

TIA
Geoff.

Geoff,
Do you Shared in the Excel sense or shared as in more the one person uses
the WB ?
Understanding the difference may help you.
Make sure you understand the consequences of <Excel Sharing before you
apply it.

NickHK

"GeoffG" wrote in message
...
I've written VBA code to open a shared Workbook and make changes. I need
my
code to determine whether another user has the Workbook open and whether,
as
a consequence, my code is opening a read-only copy of the Workbook - in
which case, the code needs to abandon making changes, with advice to the
user to try again later.

Can I programmatically attempt to open a workbook in exclusive mode and
then
see if the Workbook opened? Or is there a property I can examine after
the
Workbook has opened to determine whether it's in read-only mode?

TIA
Geoff.

Hi Nick:

I'm afraid I don't know what the Excel sense is. (If you can help me with
that or point to information on the web, I'd be grateful.)

I was referring to a Workbook stored on a network server that is shared (ie
updated) by more than one person on the network. The Workbook only allows
one person at a time to make changes.

TIA
Geoff



"NickHK" wrote in message
...
Geoff,
Do you Shared in the Excel sense or shared as in more the one person uses
the WB ?
Understanding the difference may help you.
Make sure you understand the consequences of <Excel Sharing before you
apply it.

NickHK

"GeoffG" wrote in message
...
I've written VBA code to open a shared Workbook and make changes. I need
my
code to determine whether another user has the Workbook open and whether,
as
a consequence, my code is opening a read-only copy of the Workbook - in
which case, the code needs to abandon making changes, with advice to the
user to try again later.

Can I programmatically attempt to open a workbook in exclusive mode and
then
see if the Workbook opened? Or is there a property I can examine after
the
Workbook has opened to determine whether it's in read-only mode?

TIA
Geoff.