I want to know how to change file attributes, using VBA?

Question

I have a macro in Excel 2003 that keeps track of data based on a monthly charter. What I want to do is, change the file attribute to read only, AFTER I save the file. (It doesn't do any good, if it changes it to read only then complains when you try to save, you know :P) What I would prefer to use is an if statement, like so...
If end of file
Then
prompt save
exit program
change attribute to read only

Does anyone know a way to do that? (If you weren't listening at first, I'm using Visual basic for Applications, under Excel 2003)

Answer 1

Since you didn't mention what trigger the end of file condition, I could only assume the condition as end of file

a. you click the corner x to close the file, or

b. you use a macro to close the file



my solution is when you close the file, vba prompt you whether you want to 1. close & save the file, set file to read only , 2. close but not save file.



Only if you choose 1. vba will call a script file chkro.vbs in c:\ and save the xls file then close. If you choose 2. it will close the file only.



When chkro.vbs file is called, it will wait 5 seconds(time expect the most excel needed to close the file) then set attribute of the calling xls file to read only.



Here's a demo of the above http://www.freewebs.com/swhtam/YA/chkReadOnly.xls Alt-F11 for a clear view of the complete code. Ctrl-Shift-A or click corner x will activate closing macro



Here's the http://www.freewebs.com/swhtam/YA/chkro.vbs that need to be put in c:\ unless you change the vba code, this file can be changed by right click and edit, syntax actually is vb script.



Hope this helps.

Answer 2

you do not desire to do this in Excel. incredibly, you are able to't, as Excel is a 2-dimensional array spreadsheet. you desire a relational database like get admission to. in case your reproduction of Excel is an element to the MS workplace suite, you possibly have already got get admission to. If not, you will get a reproduction seperately. get admission to ninety seven or 2000 will do the job basically nice in case you are able to hit upon a reproduction, and the two may well be extra much less costly than 2003 (even though, if it incredibly is for business enterprise, it is deductible). Then pass on line to locate an academic or a used workplace textbook (those from course technologies are very solid), or take a application purposes course at your community community college. this provide you with adequate suggestion to construct your database.

Answer 3

Have you looked at Application's WorkbookBeforeClose event? Unlike WorkBook's BeforeClose event, this will not happen before the save.



http://msdn2.microsoft.com/en-us/library/aa221023(office.11).aspx