Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Ultimate Control
I have a file that is set up so that on Before Save if it will cancel and
tell the user that you cannot save this file to another location if the user attempted a Save As. Now my question is how do you keep a user from "copying or saving" over a file with another file. Is this possible? I sure hope it is because this definitely would lock down a file. It has been my experience that excel files are always exposed to being copied over if a user has rights to that directory. Is that correct? I hope not. Thank you, Steven |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Ultimate Control
I have a file that is set up so that on Before Save if it will cancel
and tell the user that you cannot save this file to another location if the user attempted a Save As. Now my question is how do you keep a user from "copying or saving" over a file with another file. Is this possible? I sure hope it is because this definitely would lock down a file. It has been my experience that excel files are always exposed to being copied over if a user has rights to that directory. Is that correct? I hope not. Thank you, Steven It's a nice question, but it's not a nice solution as far as I can see. On any file, when you go to the right-click-properties dialog you can tick the "Read-Only" attribute. That'll stop anyone accidentally using a Saveas to clobber your file, but it does have the side-effect that when you open the file it'll be read-only, so no-one can make any changes either. You could set the read-only property dynamically every time you open/ close the file, although it's a lot of effort. You need to reference Microsoft Scripting Runtime (scrrun.dll) from tools, references. Something like this: Private Sub Workbook_Open() Dim fso As FileSystemObject Dim thisfile As File Set fso = CreateObject("scripting.filesystemobject") Set thisfile = fso.GetFile(ThisWorkbook.FullName) thisfile.Attributes = Normal ' remove read-only setting ThisWorkbook.ChangeFileAccess (xlReadWrite) ' open for editing End Sub Private Sub Workbook_BeforeClose(Cancel As Boolean) ThisWorkbook.Save ' file has to save before it becomes readonly Dim fso As FileSystemObject Dim thisfile As File Set fso = CreateObject("scripting.filesystemobject") Set thisfile = fso.GetFile(ThisWorkbook.FullName) thisfile.Attributes = Scripting.ReadOnly Cancel = False End Sub Although I've lost the "would you like to save your changes" dialog, and if someone really wanted to destroy your file, they still could. Far easier is just to keep a backup. Pip |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Ultimate Control
I have a file that is set up so that on Before Save if it will cancel
and tell the user that you cannot save this file to another location if the user attempted a Save As. Now my question is how do you keep a user from "copying or saving" over a file with another file. Is this possible? I sure hope it is because this definitely would lock down a file. It has been my experience that excel files are always exposed to being copied over if a user has rights to that directory. Is that correct? I hope not. Thank you, Steven It's a nice question, but it's not a nice solution as far as I can see. On any file, when you go to the right-click-properties dialog you can tick the "Read-Only" attribute. That'll stop anyone accidentally using a Saveas to clobber your file, but it does have the side-effect that when you open the file it'll be read-only, so no-one can make any changes either. You could set the read-only property dynamically every time you open/ close the file, although it's a lot of effort. You need to reference Microsoft Scripting Runtime (scrrun.dll) from tools, references. Something like this: Private Sub Workbook_Open() Dim fso As FileSystemObject Dim thisfile As File Set fso = CreateObject("scripting.filesystemobject") Set thisfile = fso.GetFile(ThisWorkbook.FullName) thisfile.Attributes = Normal ' remove read-only setting ThisWorkbook.ChangeFileAccess (xlReadWrite) ' open for editing End Sub Private Sub Workbook_BeforeClose(Cancel As Boolean) ThisWorkbook.Save ' file has to save before it becomes readonly Dim fso As FileSystemObject Dim thisfile As File Set fso = CreateObject("scripting.filesystemobject") Set thisfile = fso.GetFile(ThisWorkbook.FullName) thisfile.Attributes = Scripting.ReadOnly Cancel = False End Sub Although I've lost the "would you like to save your changes" dialog, and if someone really wanted to destroy your file, they still could. Far easier is just to keep a backup. I agree about the backup; however, you do not need the FileSystemObject just to change file permissions... use the built-in SetAttr Statement... Private Sub Workbook_Open() SetAttr ThisWorkbook.FullName, vbNormal ThisWorkbook.ChangeFileAccess xlReadWrite ' open for editing End Sub Private Sub Workbook_BeforeClose(Cancel As Boolean) ThisWorkbook.Save ' file has to save before it becomes readonly SetAttr ThisWorkbook.FullName, vbReadOnly End Sub The above (untested) should work fine. Rick |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Ultimate Control
Thank you for your responses. That is not what I am saying. What I want to
do is when I create a file, we will call it "File001.xls", in excel there should be a property that says that only the creater of the file can a.) "copy" over the file or b.) take a different file and save it over the file. Now, from within the file File001.xls a user can save the file but could not Save As to anything else. The ultimate key is that, for example, say a user is in File002.xls and they try to do something like save File002.xls to File001.xls then the system should say, "You cannot save over File001.xls from another file...the creater of the file has blocked the ability to overwrite the file from the outside. This file can only be updated and saved by properly opening the file and working within the file." The same would hold true if they tried to copy over the file in Explorer or in the Excel dialog box or delete the file. If this cannot be done I believe it is an overall weakness in excel that needs changed. Steven |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Ultimate Control
What you're asking for is an OS-level piece of functionality: Excel cannot
"watch" what folk do in Explorer when it's not running. This is really a job for a document management system. Tim "Steven" wrote in message ... Thank you for your responses. That is not what I am saying. What I want to do is when I create a file, we will call it "File001.xls", in excel there should be a property that says that only the creater of the file can a.) "copy" over the file or b.) take a different file and save it over the file. Now, from within the file File001.xls a user can save the file but could not Save As to anything else. The ultimate key is that, for example, say a user is in File002.xls and they try to do something like save File002.xls to File001.xls then the system should say, "You cannot save over File001.xls from another file...the creater of the file has blocked the ability to overwrite the file from the outside. This file can only be updated and saved by properly opening the file and working within the file." The same would hold true if they tried to copy over the file in Explorer or in the Excel dialog box or delete the file. If this cannot be done I believe it is an overall weakness in excel that needs changed. Steven |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
The Ultimate Problem | Excel Programming | |||
My ultimate goal is to create an ID that will always be unique | Excel Programming | |||
My ultimate goal is to create an ID that will always be unique | Excel Programming | |||
The ultimate Reference Guide to VBA in Excel | Excel Programming | |||
Ultimate protection | Excel Discussion (Misc queries) |