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

[6diegodiego9]

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?

 

[bobsan42]

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):

    Open filePath For Random Lock Write As #10

Problem is no one cannot save the file before it is closed:

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]

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):

    Open filePath For Random Lock Write As #10

Problem is no one cannot save the file before it is closed:

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]

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]

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]

You can keep the Front-end in a shared folder and anyone who needs it can copy it to his/her computer.

Is it possible to open it (front-end) directly on the shared folder?

 

[bobsan42]

Is it possible to open it (front-end) directly on the shared folder?

Of course

 

[6diegodiego9]

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]

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 ...

 

[Marc L]

Access is buggy as well

No as Access is well designed to work with mutli users on network …​