I am guessing you want users to have access to the workbook, but you don't want to allow it to be over written.
Have you thought about holding the workbook on a server and make it, and the folder it is in, read only.
You can also password protect portions of it, to stop overwriting. This method does not stop users saving it locally and then working on it. It will only stop the master copy being changed before other users want to access it.
Save is an internal function of excel, so I think it might be a complex and, possibly, unwanted way of disabling save. I have never seen, or heard, of it being done, if it could be, then i think Microsoft would have reference to it, in their knowledge base. It would involve trapping the save function call, and redirecting it, in assembler. Not a task for novices, and it might mess up functionality of excel.
I do this regularly. I have a number of Excel workbooks on the server with literally 100+ "reports" driven by SQL. IE: my own query system.
I do NOT want my users to save these workbooks. Why?
1. They are VERY large (huge VBA code structure).
2. If they save them, they could reexecute code which has been changed/fixed/improved in the current version.
3. It would allow my code in the hands of unauthorized users.
My users are trained to copy the worksheet (already flat data, no formulas other than occasional formatting) to a new workbook and save THAT to their area.
This technique has worked great for 10 years!
You must:
1) Make the workbook read-only in your "production directory" (as suggested by Chippy088) AND
2) Disable the Save/Saveas function in VBA AND
3) Detect the directory you are running in and only allow resaving the program on exit IF you are in your "development directory".
To "publish" new code, copy the dev xls to the production directory and make it read-only.
No one has found a way to get around this so far.
Gary
PS: I would have provided some code but I did this so long ago it will take me sometime to dig it out. However, if you need - respond and I will do so.
Free Guide: Managing storage for virtual environments
Complete a brief survey to get a complimentary 70-page whitepaper featuring the best methods and solutions for your virtual environment, as well as hypervisor-specific management advice from TechTarget experts. Don’t miss out on this exclusive content!
Discuss This Question: 3 Replies