Excel 2016 opens SharePoint workbooks as read-only


When opening a workbook in SharePoint using Excel 2016, it opens as read-only with the following message:
 

We opened this workbook read-only from the server.


To edit the workbook, click on the button Edit Workbook

This is a design change from Excel 2013, which opened workbooks directly into edit mode by default. The reason for the change in Excel 2016 is to prevent locking out users from editing the file.

When you open a file in edit mode, you get exclusive editing capability in the file, and no other person can then open the file to edit. They can only view the spreadsheet in read-only mode. Even if you are the only person that uses the workbook, this can happen when you open a file in edit mode and leave it open on your work machine, for example, which means you are locked out of the file for editing on another computer at home or elsewhere.

Changing the behavior to open workbooks by default in read-only mode helps prevent these scenarios. Many users read information but never make edits to the spreadsheet. For these users, opening the file as read-only works just fine for them, and it leaves the file unlocked for editing for someone else. So this change in behavior helps keep the workbook available for those people who need to edit.


Comments (44)

  1. Austin says:

    It seems that in this scenario, any macros that are set to auto_open for this file are not triggered. Is this also intended behaviour?

  2. Jon says:

    Is there a way to disable this to open workbooks by default? My colleague and I open dozens of workbooks every day to edit them. There is not concern about locking out another user. This feature is incredibly frustrating and inconvenient. How is this not
    easily changed?

  3. Pafka says:

    Such a significant change in functionality should be configurable! Many users easily miss the yellow bar in Excel that the file is opened as read-only so they start editing the file without click on it. But after they make their changes, they realize the
    file is locked by someone else so they start saving the files locally, overwriting later, etc….

    There’s nothing more unfrinedly than when a button "edit in Excel" in SharePoint opens a file as Read-Only – how stupid is that?! Please at least make the recent change configurable.

  4. alex says:

    It’s very strange that "enhancement" was added, it’s very annoying for end users. Even more strange is that Word opens document as usual, and it relates only to Excel. And the worst is that the feature breaks MS-EXCEL Schema URIs (https://msdn.microsoft.com/en-us/library/office/dn906146.aspx#sectionSection9)

  5. Serena says:

    I agree this is definitely NOT an enhancement. For over 20+ years the standard network/office version of Excel has by default allowed the first one in to edit directly and the second one in is read-only. This change is an enormous speed bump for most excel
    users.

  6. bill says:

    I agree with all of the above users. This is NOT an improvement. Please make "Open in Read-Only Mode" an option, not a requirement. And, make it the secondary option; i.e. only enabled the option if the user turns it on from within Excel Options. Thanks.

  7. MaruIT says:

    I agree with all of the above users. This is a huge step backwards. Please make "Open in Read-Only Mode" an option, and not a requirement. And, make it the secondary option to allow this option by URL or via Internet vs Intranet.

  8. Sebastien says:

    Any way around this yet? It’s incredibly frustrating.

  9. Dale says:

    I am adding my annoyance at this change as so many before me have done. I much prefer the old way or at the very least the "read only" mode needs to be an option.

  10. Bart says:

    Please remove this new feature or make it adjustable. Our service desk is only getting complaints about it. It is even worse when the files contains external links. Excel first wants to update them before you can enable editing.

  11. Kendall says:

    I found a workaround. Go into the VBA module (press Alt-F11). Double click on ‘ThisWorkbook’ and add the following lines:

    Private Sub Workbook_Open()
    ActiveWorkbook.LockServerFile
    End Sub

    You will need to save as a macro-enabled workbook (.xlsm), and do this for every SharePoint file you want to automatically open as Read-Write with Excel 2016… but it works!

    1. Glenn Bamford says:

      Thanks Kendal – I thought I’d be stuck on this for hours trying to find a workaround, but found your hint in 2 minutes, thank goodness.

    2. Dave says:

      YES! Awesome fix! 🙂

  12. julie smith says:

    Kendall, THANK YOU SO MUCH! Life saver.

  13. Jen says:

    Agreed. This is super annoying. The likelihood of me opening a file just to look at it and not to change anything is slim to nil.

  14. Ranjit Singh Kumar says:

    This feature in the so-called new version is very very annoying, especially when we need to open a workbook using macros and make some changes.
    Anyways, the solution from Kendall worked well.
    Thank you so much – This really is a life save for Office users.

  15. Nic Fletcher says:

    Hmm…

    … Great solution from Kendall, but can anyone explain why on one computer always opens directly in edit mode, and my other computer only opens in Read-only mode (even if it’s not really that hard to click the "edit" button)?

    Both have Windows 10 (one Enterprise, the other pro), and both have Office 2016 from the same Click-to-run Office365 for Business source… albeit the Office version on Enterprise is one track behind on updates). BTW, it’s the Enterprise / One track behind
    that opens the files normally.

  16. E-Rock-001 says:

    I can see the logic in this new default behavior, but there needs to be an option to switch it back for some of our users.

  17. Show us the option to switch back. says:

    This is a major – major inconvenience.

  18. Georgio says:

    I need to agree with what was already said above. What an annoying "improvement". Also, it breaks Excel Schema URIs as someone already said. Who on Excel team even came up with this idea? Are you guys out of your mind?

  19. TheReluctantHero says:

    I had to modify the workaround since I was testing some VBA code in both 2013 Excel Version and 2016 Excel Version on Office 365 .

    seems like Activeworkbook.LockServerFile fails on 2013 version but if you throw in

    If Application.Version = 16 then
    ActiveWorkbook.LockServerFile
    Else ‘Under Excel 2016 version
    end if

    That seems to work for 2016 versions of Excel since it seems to be happening on there only. I did not try on any other Excel versions or Office/SharePoint configurations.

    1. Mary says:

      Can you give me the whole VBA script for it to work on 2013 and 2016 documents?

      is it

      Private Sub Workbook_Open()
      ActiveWorkbook.LockServerFile
      If Application.Version = 16 then
      ActiveWorkbook.LockServerFile
      Else ‘Under Excel 2016 version
      end if
      End Sub

      or is it:

      Private Sub Workbook_Open()
      If Application.Version = 16 then
      ActiveWorkbook.LockServerFile
      Else ‘Under Excel 2016 version
      end if
      End Sub

  20. Bluesky63 says:

    Hi Kendall, the code work perfectly, however, do you have a workaround to disable the prompt when we embed link of xlsm in webpages

    “Do you want to open test.xlsm from http://XXXXXXXXXXXXXXX OPEN CANCEL

    Thank you

  21. There is a permanent solution in order of preference:
    1. MS reverse this incredible stupid idea back to what is was
    2. Make a AddIn (xlsam) with a class named clsApp (or whatever you like) that contains the following code:

    Option Explicit

    Public WithEvents AppEvents As Application

    Private Sub AppEvents_WorkbookOpen(ByVal Wb As Workbook)
    On Error Resume Next
    If Val(Application.Version) = 16 Then
    ‘Stop
    If Not Wb.IsAddin And Wb.ReadOnly Then
    Debug.Print Now, Wb.Name
    Debug.Print “ReadOnly before: ” & Wb.ReadOnly

    Wb.LockServerFile

    Debug.Print “ReadOnly after: ” & Wb.ReadOnly

    End If

    Else ‘Under Excel 2016 version
    End If

    End Sub

    And add this piece of code in the ‘ThisWorkbook’ module of the add in:
    Private Sub Workbook_Open()
    Set XLApp = New clsApp
    End Sub

    Add the add-in to your loaded Add-Ins (File, Options, Add-ins, Manage [Excel Add-ins] and browse for you addin

    Restart Excel.
    Now every time you open a ReadOnly, non-addin Workbook, the LockServerFile is fired.
    Problem solved.

    Greetings from Amsterdam
    Martin

    1. Igor Golubochanski says:

      Martin Drenth – your solution not working. should I add the add in to one file where I ran the macro or for all the files?

    2. Greg says:

      Martin, I can’t quite get your code to work– I’ve created the add-in but everything is still opening as read-only in Sharepoint. could you explain the different stanzas of code so that I can attempt to tweak things to get it to work? I’m using Excel 2016.

  22. Simon says:

    I can accept the reasoning behind opening as “read only” to save exclusive lock out, but if you’re going to do that why not make the workbook read only. As in: you can’t make changes until you expressly want to? I’ve had a couple of occasions where fixated on making a quick entry into a spreadsheet with multiple files open at once you simply don’t notice the warning and happily make edits to a workbook only to realise upon trying to save the file that there’s a problem. Even a simple “it looks like you’re trying to make changes to this workbook” might be palatable (just).

  23. STGdb says:

    Yet another example of Microsoft not thinking like an “Enterprise”. If MS is going to change something like this (“enhancement”), then they need to consider the impact that it has on the end user. Not having an option to turn this “enhancement” off is just another example in a long line of screw ups by MS recently.

  24. William says:

    Echoing all the above comments and frustrations, my rhetorical question is why does the argument only apply to Excel and not all Office documents. If it is deemed appropriate for users to be able to open a SharePoint file in read-only mode, why not merely add that option “View document in Excel”. The article documenting the new feature cleverly avoids the obvious paradox that the user selecs “Edit document in Excel”, which is documented as being for users who want to “Open” the document. The menu states “Edit”, not “Open”. And I love the “We” opened the file in read-only mode despite “you” literate Excel user selected “Edit”!! The reason for the feature is a legitimate one, but should have been addressed in SharePoint, not in Excel. UI incompatibilities like this are extremely impactful on organizations, especially ones with a lot of code. In VBA, file “.Open ReadOnly:=False” ought to be totally unambiguous without requiring a fix. Or the VBA documentation should be modified to document that ReadOnly = False is ignored when opened from server

  25. A. Hansen says:

    I feel compelled to add my 2 cents. This is NOT an improvement – it just adds lots of keystrokes to my poor arm/write. Please make “Open in Read-Only Mode” an option, not a requirement. And, make it the secondary option; i.e. only enabled the option if the user turns it on from within Excel Options.

  26. Kerston says:

    Another idiotic change from the arrogant ‘geniuses’ in MS product development – must be related to the guys who thought a tablet interface was a good thing to put on a server OS. Who in the world thinks it’s OK to just change stuff like this and force it on tens of millions of user with billions of hours of experience in the app??? We need for the ‘We feel your pain’ video that came out to the partners a few years ago to be a real thing.

  27. Gambill says:

    I agree with the other comments that this is not an enhancement. I have vba that runs and updates many sharepoint files. This is stopping the process. Even setting read only to false and editable to true in the vba does not trigger this to kick back. 🙁

  28. Uwe says:

    A great disimprovement again, like some others in 2016.
    And I thought you can’t get it more intricately after some 2013 improvements. I’m so naive!

    Thanks, Kendall, for your trick. Although it pisses off all those users who want the read only mode on purpose, because they know, I am already first in the file. Now they have to do the extra clicks.

  29. Rob says:

    This ‘enhancement’ appears to have been made in a vacuum, or at least only asking a few people. For the vast majority of my end-users here this would be incredibly confusing to have to choose to ‘edit’ twice: SharePoint presents the Edit / Read Only choice, then Excel ignores it.
    Excel 2016 message might as well say “I don’t think you knew what you were doing one second ago; I’m going to open in Read Only mode because you’re an idiot”.

  30. Tee Carter says:

    Almost every comment in response to this article is asking for this “feature” to be an option. It’s been almost a year now, where’s the option to disable this?

  31. Sean Donovan says:

    It is really annoying. I have to open and close lots of spreadsheets and it is just another annoying “feature”. How to turn off and get back to normal.

    1. Igor Golubochanski says:

      Martin Drenth – your solution not working. should I add the add in to one file where I ran the macro or for all the files?

  32. Tom says:

    This is not an improvement. It’s a massive inconvenience. Whoever endorsed this change isn’t from the real world.

  33. Dion V says:

    Absolutely a terrible decision. This is causing nothing but grief after we have switched from our internal file server to OneDrive/Sharepoint. Every Excel file that gets opened must have “Edit Workbook” clicked to work on it, and the [Read Only] status in the title bar does not always go away, but user’s often don’t notice that until they’ve made many changes and try to save. We are using current versions of Office 2016 and OneDrive, and there does not seem to be an option to change this default behaviour. And we’re into 2017 now.

  34. frustrated says:

    Our marketing department has business process which uses Excel with VBA code which auto executes and presents a form.
    For the user to make decisions and saving the document – but never getting direct access to the document.
    Due to JSOX compliance rules.
    The VBA code autoruns and connects with our CRM and GreatPlains and other databases to fill in fields.
    This has been working for years.

    And it is now broken with Excel 2016.

    Microsoft, we don’t need your help preventing users from getting locked out of files on Sharepoint.
    We know our business needs better than you!!!
    Sharepoint already has a feature which displays who has the file open when the library is set for version control.
    In our case, we want users locked out.
    If someone really needs to get into a file urgently, they ask the person who has it open to check it in.

  35. A poor excuse for not having a full collaboration toolbox implemented in Office, such as Google Docs. Rewrite the core, copy Googles approach and we have the perfect office suit on the market. Oh, and while you’re at it, kill off SharePoint too please.

    When will Microsoft actually start producing software that’s up to date with how we work today? It’s really not that hard to make the right decisions in the software industry these days. Trends are extremely visible and so are the user requirements.

    BR

  36. JorgeRaigoza says:

    Make sure you guys vote here to request this change: https://excel.uservoice.com/forums/304921-excel-for-windows-desktop-application/suggestions/10205019-read-only-mode

    This request was placed in 2015, but only has 71 votes as of 6/14/2017.

  37. Omegacron says:

    This is really bad – we finally broke down and started rolling out Office 2016 to our users, and now find that this new “feature” is breaking our business processes. We have several in-house apps that automate spreadsheet functions, and those apps can no longer update an existing spreadsheet because the file opens in read-only.

Skip to main content