how to avoid Excel FILE deletion (on network) while allowing modification

6diegodiego9

Board Regular
Joined
Jan 9, 2018
Messages
54
Office Version
  1. 2016
Platform
  1. Windows
I manage an xlsx file on a network share which is "protected and shared" so that multiple users can simultaneously modify it.
While the shared mode offers a fantastic protected "track changes" feature that allows me, it still has a BIG SECURITY FLAW: anyone can freely DELETE the entire file or even substitute it with another file!

In my attempts to find a security solution with NTFS permissions, I hit against the problem that if I deny the deletion of it I would unwantedly deny modification as well because Excel deletes and recreates the file each time a "save" is done.

A collegue who is an Access-evangelist, tells me that that I should pass to an Access solution with local front-end Access apps on every client PC and just the database on the network share in a path unknown to the client users (hidden in protected VBA code). However I hate the idea to create, deploy and manage local apps just to solve this simple problem. I'm a VBA developer and I don't know Access anyway.

Is it really impossible to find a solution or at least some trick to avoid an Excel FILE deletion (on network share) while allowing its (Excel tracked) modification by any user?
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

bobsan42

Well-known Member
Joined
Jul 14, 2010
Messages
1,390
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
Platform
  1. Windows
Less than a month ago I ditched the idea of using a shared workbook as a backend for a project. For too many reasons and now you added another one.
I could not believe what I am reading, so I checked an turned out you are right to some extent. You can actually delete it anywhere - even on local drives. Actually I did check before that a shared WB is not locked for editing while opened by several users. So what I am thinking is that the WB does not get opened really only Its in-memory copy or a temp file copy.
VBA is not just an EXCEL thing, so a VBA developer can try and do more than just coding for excel - Word, Access, Outlook, Project, Visio, Powerpoint, AutoCAD and a few more AFAIK. I quite like working with VBA. And you can use any of the apps you need to do what it is designed to do.
From my Access projects I have this attitude to separate front- and back-ends. Keep the data as far away from the user's hands as possible.
Designing and developing some things in access is quite tedious though, so enevitably i got to the point where I am now using Excel as my front-end and the back-end is an Access db file hidden from the user. In addition it handles data and multi-user operations natively and (sorry to say) much better than excel shared wb.

One way to lock the file (from within VBA):
VBA Code:
    Open filePath For Random Lock Write As #10
Problem is no one cannot save the file before it is closed:
VBA Code:
Close #10
So before each save you will have to check if the file is writable, then invent a way to negotiate its unlocking and after it is saved - relocking. And all this time you will need a computer with Excel (or any other software) running and keeping this file from deleting.

I don't know about you, but for me I decided I don't have the time to reinvent databases and servers :)
Sorry I could not be more helpful.
 

6diegodiego9

Board Regular
Joined
Jan 9, 2018
Messages
54
Office Version
  1. 2016
Platform
  1. Windows
Less than a month ago I ditched the idea of using a shared workbook as a backend for a project. For too many reasons and now you added another one.
I could not believe what I am reading, so I checked an turned out you are right to some extent. You can actually delete it anywhere - even on local drives. Actually I did check before that a shared WB is not locked for editing while opened by several users. So what I am thinking is that the WB does not get opened really only Its in-memory copy or a temp file copy.
VBA is not just an EXCEL thing, so a VBA developer can try and do more than just coding for excel - Word, Access, Outlook, Project, Visio, Powerpoint, AutoCAD and a few more AFAIK. I quite like working with VBA. And you can use any of the apps you need to do what it is designed to do.
From my Access projects I have this attitude to separate front- and back-ends. Keep the data as far away from the user's hands as possible.
Designing and developing some things in access is quite tedious though, so enevitably i got to the point where I am now using Excel as my front-end and the back-end is an Access db file hidden from the user. In addition it handles data and multi-user operations natively and (sorry to say) much better than excel shared wb.

One way to lock the file (from within VBA):
VBA Code:
    Open filePath For Random Lock Write As #10
Problem is no one cannot save the file before it is closed:
VBA Code:
Close #10
So before each save you will have to check if the file is writable, then invent a way to negotiate its unlocking and after it is saved - relocking. And all this time you will need a computer with Excel (or any other software) running and keeping this file from deleting.

I don't know about you, but for me I decided I don't have the time to reinvent databases and servers :)
Sorry I could not be more helpful.

Hi bobsan42, thanks for your time in answering :)

I didn't understand your solution. Did you distribute your Excel front-end to every user? this would not be a viable option for me because I don't even know how many and who they are...
 

bobsan42

Well-known Member
Joined
Jul 14, 2010
Messages
1,390
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
Platform
  1. Windows
In short - yes.
I normally distribute a front end to every user. The data is kept in the the back - it my be a database or a shared excel wb. It may be opened hidden or simply connected. Or in a project I kept open as read-only to provide access to the data and only open it when data needs to be saved. There can be many solutions.
You can still use a shared WB as front-end but there are limitations with shared WBs.
You can keep the Front-end in a shared folder and anyone who needs it can copy it to his/her computer.
In some cases even if the front-end is opened as read-only it should not be a problem.
But of course I am not aware of your environment and circumstances.
 

Marc L

Active Member
Joined
Apr 5, 2021
Messages
430
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

A collegue who is an Access-evangelist, tells me that that I should pass to an Access solution
He is 200% right ! Multi users on a network, forget Excel …​
 

6diegodiego9

Board Regular
Joined
Jan 9, 2018
Messages
54
Office Version
  1. 2016
Platform
  1. Windows
He is 200% right ! Multi users on a network, forget Excel …​
Another thing I don't understand is: given that shared workbooks are supposed to work with multiple concurrent users on a shared folder, and it's sometimes buggy, why I should not make the assumption that Access is buggy as well in that situation? (eg. I read about corrupted databases...)
 

bobsan42

Well-known Member
Joined
Jul 14, 2010
Messages
1,390
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
Platform
  1. Windows
why I should not make the assumption that Access is buggy as well in that situation? (eg. I read about corrupted databases...)
So far nothing is perfect, and you can always have corrupted data in ANY system for various reasons. That's why backups are used so much.
It's just that shared WBs are a compromise, an afterthought, they are just not properly made for this task.
But of course it all depends - sometimes you just don't need all that troubles with databases ...
 

Watch MrExcel Video

Forum statistics

Threads
1,129,387
Messages
5,636,018
Members
416,892
Latest member
Bensch

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top