locked
How can I check if an excel workbook is opened in server read only mode on SharePoint (using excel macro)? RRS feed

  • Question

  • I have an Excel workbook with macros running on SharePoint. SharePoint will open the workbook in server read only mode if another user has it open.  The workbook automatically saves when the user exits. I coded it that way. How can I check to see if the workbook is opened in server read only mode so I don't automatically save the workbook?  ActiveWorkbook.ReadOnly and ThisWorkbook.ReadOnly returns False.
    Monday, October 31, 2011 3:04 PM

Answers

  • I've done some research on this problem and apparently this is a bug. The WorkBook.ReadOnly property should be returning True but it doesn't when a workbook is opened from Sharepoint. I've no idea on the timeline to fix it, but there is a workaround you can use.

    You can use the InString (INSTR) method to do a search of the application.caption value to look for "Read-Only" in the text. If it exists, then you can tell your code to do whatever you need it to do. Here is a code sample:

    If InStr(1, Application.Caption, "Read-Only") > 1 Then
        MsgBox "Is read only"
    Else
        Msgbox "Is not read only"
    End If

    Will Buffington
    Microsoft Excel Support

    Friday, November 4, 2011 9:34 PM

All replies

  • Those are the right things to check in code. Though one thing to consider when opening a document as read only from SharePoint. The code will not run since the document needs to be in Edit mode. This is in part of the security of Office and how it handles documents when they open. Basically a document that is read-only/non-edit mode won't be able to utilize the VBA/macros.

    I don't think any of the Auto{name} modules will work either since there isn't one that determines when the document is ready for editing.


    -victor

    Please remember to indicate if your question/comment has been answered.

    Thursday, November 3, 2011 1:31 PM
  • I've done some research on this problem and apparently this is a bug. The WorkBook.ReadOnly property should be returning True but it doesn't when a workbook is opened from Sharepoint. I've no idea on the timeline to fix it, but there is a workaround you can use.

    You can use the InString (INSTR) method to do a search of the application.caption value to look for "Read-Only" in the text. If it exists, then you can tell your code to do whatever you need it to do. Here is a code sample:

    If InStr(1, Application.Caption, "Read-Only") > 1 Then
        MsgBox "Is read only"
    Else
        Msgbox "Is not read only"
    End If

    Will Buffington
    Microsoft Excel Support

    Friday, November 4, 2011 9:34 PM