We help IT Professionals succeed at work.

Excel shared spreadsheet

118 Views
Last Modified: 2017-05-10
We keep a spreadsheet on our SBS2008 server
If someone is working on the file and another person is opening it as read only, we can´t save the working copy.
is there a way to stop this and enable SAVE on the working copy?
Comment
Watch Question

Flora EdwardsMedicine

Commented:
if you have excel 2016 365 then you can use co-authoring feature, it also works with excel online.

Author

Commented:
some users with Excel 2010 and some with 2016
Tom CieslikIT Engineer
CERTIFIED EXPERT
Distinguished Expert 2017

Commented:
In Excel 2010 and 2013 you can;t do anything since if you will made some changes to document Excel must delete original document and save a copy to same file name but first must have access to file to delete it. If someone will open document for read only in same time, then document can't be deleted so new version can't be saved.
There is no way to save under same name but you can use save as and rename document for example  document-copy1.xlsx
It's the only way.

Author

Commented:
is there a way to stop someone opening a file that is being edited
Tom CieslikIT Engineer
CERTIFIED EXPERT
Distinguished Expert 2017

Commented:
I don't think so, but your company can make rules that if someone will get information that document is in use and can be open in read only, then they should close document.

Only one way to do it i believe is implementing a Share Point and place document in share point, then share point will inform who is using document.
Roy CoxGroup Finance Manager
CERTIFIED EXPERT

Commented:
Shared workbooks really should be avoided because of such issues, unless you are using Office 365 as stated earlier.

Author

Commented:
Thanks for your help
Group Finance Manager
CERTIFIED EXPERT
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
Thanks Roy
but I am not that technical how do i/and where do i use this code please
Roy CoxGroup Finance Manager
CERTIFIED EXPERT

Commented:
This code goes into the workbook event code, see below to copy & paste to your workbook.

Add Excel VBA Code to a Workbook Module

Copy the Excel VBA code that you want to use
Select the workbook in which you want to store the code
Hold the Alt key, and press the F11 key, to open the Visual Basic Editor
In the Project Explorer, find your workbook, and open the list of Microsoft Excel Objects
Right-click on the ThisWorkbook object, and choose View Code
Where the cursor is flashing, choose Edit -> Paste
Copy Excel VBA Code From a Different Workbook

This code is worksheet event code

Private Sub Worksheet_Change(ByVal Target As Range) 
    If EndTime Then 
        Application.OnTime _ 
        EarliestTime:=EndTime, _ 
        Procedure:="CloseWB", _ 
        Schedule:=False 
        EndTime = Empty 
    End If 
    EndTime = Now + TimeValue("00:10:00") 
    RunTime 
End Sub 

Open in new window


Add Excel VBA Code to a Worksheet Module

Some code is Event code, and will run automatically when a specific action occurs in  the worksheet For example, if you type in a cell, and press the Enter key, the worksheet has been changed. This would trigger the Worksheet_Change event. Worksheet event code is stored on a worksheet module. To add it to your worksheet, do the following:

Copy the code that you want to use
Select the worksheet in which you want the code to run
Right click on the sheet tab and choose View Code, to open the Visual Basic Editor.Where the cursor is flashing, choose Edit -> Paste

The rest of the code needs to be in a standard module, see how to add a module below.
'If no changes have occurred then this runs 
Public EndTime 
Sub RunTime() 
    Application.OnTime _ 
    EarliestTime:=EndTime, _ 
    Procedure:="CloseWB", _ 
    Schedule:=True 
End Sub 
 
Sub CloseWB() 
    Application.DisplayAlerts = False 
    With ThisWorkbook 
        .Saved = True 
        .Close 
    End With 
End Sub 

Open in new window


The instructions are available here and may be clearer to read  with the images

Author

Commented:
thank you the code worked perfectly
Roy CoxGroup Finance Manager
CERTIFIED EXPERT

Commented:
Pleased to help
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.