2

Suppose a workbook exists on a shared network drive, and is open by User A. If User B attempts to open this file by double clicking it, he gets something like the following prompt:

enter image description here

If User B instead attempts to open the workbook programmatically (perhaps with an add-in) using:

Workbooks.Open("N:\path_to_workbook\workbook.xlsx")

what happens, exactly? Does this above prompt appear? If so, can it be suppressed with Application.DisplayAlerts = False? If Workbooks.Open as shown above throws an exception in this case, do we need to try again using the ReadOnly argument, as:

Workbooks.Open("N:\path_to_workbook\workbook.xlsx", ReadOnly:=True)

I have read other posts (including this one) that suggest that Workbooks.Open in the first code snippet succeeds and the workbook is opened read-only, although this is not perfectly clear.

Unfortunately, I don't have a test environment configured to replicate this scenario myself, which would otherwise be the obvious answer.

Community
  • 1
  • 1
OfficeAddinDev
  • 1,105
  • 3
  • 15
  • 29
  • you don't have a test environment? can't you just test the concept in a random excel file? The `ReadOnly` argument should do the trick. – Scott Holtzman Oct 06 '15 at 13:14
  • The prompt appears, and remains to appear, even if `DisplayAlerts` are turned off. If you include `ReadOnly=True` it will then open the workbook without the prompt. – luke_t Oct 06 '15 at 13:15
  • @ScottHoltzman, no, per my post I do not have a test environment that includes a shared network drive and another user. No, you cannot test this in a random workbook; try double clicking a workbook in Windows Explorer that is already open. Yes, the ReadOnly argument would certainly do the trick, but defaulting to opening workbooks read-only is undesirable. – OfficeAddinDev Oct 06 '15 at 13:37
  • @Iturner, if this is indeed correct, then please post it as an answer. I must admit that I am skeptical that you get the prompt when you try to open the workbook programmatically, but since I cannot test it myself... – OfficeAddinDev Oct 06 '15 at 13:41
  • You can run a check to see if the workbook is open first, and if it is, then open it `ReadOnly`, if that is acceptable, or just alert the user that the workbook is open and can't be worked with at the moment. See this post for more, if it helps: http://stackoverflow.com/questions/9373082/detect-whether-excel-workbook-is-already-open – Scott Holtzman Oct 06 '15 at 13:43
  • @ScottHoltzman, that is the post to which I linked in my OP, and seeks to answer a different question. I don't need to check whether the file is open before trying to open it depending on the answer to my question. – OfficeAddinDev Oct 06 '15 at 13:49
  • Apologies, I did not open that link. My thought was that you can open as ReadOnly *only* if need be, based on your last statement in your last comment. – Scott Holtzman Oct 06 '15 at 13:56
  • You've got a lot of tags on this post. When I open a workbook via VBA and someone else on the network has it open, it opens as read-only with no prompt. I have no idea about C# or VB.Net. – Dick Kusleika Oct 06 '15 at 14:17
  • Thanks, Dick. The relevant code is basically the same for VBA, VB.NET, and C#, hence the tags. Accordingly, the result that you observed would be the same for C# and VB.NET. For everyone's benefit, I want to point out that this directly contradicts what @Iturner suggested above. – OfficeAddinDev Oct 06 '15 at 14:28
  • Are you asking if you can "get around" the popup and have two people editing the workbook simultaneously by opening it programmatically? If so, the answer is **no**. – Blackhawk Oct 07 '15 at 13:25
  • @Blackhawk, not asking anything other than what is asked in the OP. – OfficeAddinDev Oct 07 '15 at 13:56

1 Answers1

1

I tested on our network drive environment, and found the following. If the file is opened by User A and User B attempts to open the file programmatically using Workbooks.Open then the result will be a Read Only version of the file.

If you wish to control the opening of Read Only files than you may utilize the following code example to close it:

Sub Test()

Dim wb As Workbook

Set wb = Workbooks.Open("Path_To_Excel_File")
If wb.ReadOnly Then
    MsgBox "File already in use"
    wb.Close savechanges:=False
End If

End Sub
mongoose36
  • 799
  • 4
  • 14