Shared Workbook

[GeoffG]

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.

 

[Mark Ivey]

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

 

[Mark Ivey]

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

 

[NickHK]

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]

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

 

[GeoffG]

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

 

[Mark Ivey]

Geoff,

I think I found a better solution @ http://support.microsoft.com/kb/291295

Please see below:

'**************************************************************

Sub TestFileOpened()

' Test to see if the file is open.
If IsFileOpen("c:\temp\MyWorkbook.xls") Then
' Display a message stating the file in use.
MsgBox "File already in use!"
'
' Add code here to handle case where file is open by another
' user.
'
Else
' Display a message stating the file is not in use.
MsgBox "File not in use!"
' Open the file in Microsoft Excel.
Workbooks.Open "c:\temp\MyWorkbook.xls"
'
' Add code here to handle case where file is NOT open by another
' user.
'
End If

End Sub

' This function checks to see if a file is open or not. If the file is
' already open, it returns True. If the file is not open, it returns
' False. Otherwise, a run-time error occurs because there is
' some other problem accessing the file.

Function IsFileOpen(filename As String)
Dim filenum As Integer, errnum As Integer

On Error Resume Next ' Turn error checking off.
filenum = FreeFile() ' Get a free file number.
' Attempt to open the file and lock it.
Open filename For Input Lock Read As #filenum
Close filenum ' Close the file.
errnum = Err ' Save the error number that occurred.
On Error GoTo 0 ' Turn error checking back on.

' Check to see which error occurred.
Select Case errnum

' No error occurred.
' File is NOT already open by another user.
Case 0
IsFileOpen = False

' Error number for "Permission Denied."
' File is already opened by another user.
Case 70
IsFileOpen = True

' Another error occurred.
Case Else
Error errnum
End Select

End Function

 

[GeoffG]

Hi Mark,

Many thanks for this solution. It uses VBA in a straightforward way to get
the result. I had found something similar (which uses API functions) on
page 462 of the book "VBA and Macros for Microsoft Excel" by Bill Jelen and
Tracy Syrstad. There doesn't appear to be any particular advantage in using
API functions.

I did wonder whether both solutions suffered from a potential problem that
may only arise on very rare occasions. It would seem there is a remote
possibility that someone on the network might open the file in the split
second between the file being closed and re-opened by the code. I suppose,
to be absolutely sure, I could see if the file is read-only after using the
"Workbooks.Open" method and abandon changes if it is. I would have thought
that a better approach would have been to open the file in exclusive mode,
but I can't figure whether the Open method allows this.

It seems a pity that there appears to be no VBA method or property that
returns the user's name who has the Workbook open on the network.

Incidentally:

1. I think I read some misleading info about the WriteReservedBy
property. I think I read somewhere that it returns the name of the other
user on the network who has the file open, but this appears not to be the
case. It appears it returns the name of the person who saved the Workbook
so that only that user can write to the Workbook in future. I have not
found any information to tell me what the user does at the user interface
while saving the Workbook to set and unset the WriteReservedBy property.
I'd like to get to the bottom of that mystery!

2. As you no doubt know, it seems we should now use Err.Raise, rather
than the backwardly-compatible Error statement (at the end of the
IsFileOpen() function). (I know you copied the sample code as is from the
Microsoft website.)

I appreciate your help. Thanks again.

Geoff