PDA

View Full Version : [SOLVED] Removing read-only status before opening the workbook



xluser2007
11-25-2008, 08:58 PM
Hi All,

I have an assumption workbook e.g. "S:\Client\Assumptions.xls".

Now, sometimes people need to open this workbook up to view the data inside. As I don;t want anyone else to change it, I go to windows Explorer, and in properties I set the status to "Read-only".

Now I have another workbook, in which I open up "S:\Client\Assumptions.xls, make changes and close.

Till now, I have to keep changing the "Read-only" status off using Exlporer before I run the macro.

Is there a simple way to toggle off Read-only as I open the workbook e.g.


Sub test()
Set wb = Workbooks.Open("S:\Client\Assumptions.xls", UpdateLinks:=0)
' What code can I set above to set read-only status to false?
End Sub
Any help is appreciated.

xluser2007
11-25-2008, 09:03 PM
BTW,

I tried:


Sub test()
Set wb = Workbooks.Open("S:\Client\Assumptions.xls", UpdateLinks:=0, ReadOnly:=False)
' What code can I set above to set read-only status to false?
End Sub
This did not set the Read-only status to False.

Any ideas

Ischyros
11-25-2008, 10:25 PM
The readonly property can only display whether it is set to true or false. I would recommend playing around with the changefileaccess method ("ActiveWorkbook.ChangeFileAccess xlReadWrite"). I could not get it to work when the workbook is open, which seems to be your dilemma as well. If I figure out anything clever, aside from using the saveas method to create another instance then I will repost. There is all a SetAtt method you could look into. If you figure it out before me post the answer! Best of luck.

xluser2007
11-25-2008, 11:16 PM
Hi Ishcyros,

Interesting, I actually tried that method as follows for testing (I was away for a little while so couldn't post earlier).



Option Explicit

Sub test()
Dim wb As Workbook
Set wb = Workbooks.Open("S:\Client\Assumptions.xls", UpdateLinks:=0, ReadOnly:=False)
wb.Sheets(1).Range("A1").Activate
ActiveCell.Value = 1
' Application.DisplayAlerts = False
wb.ChangeFileAccess Mode:=xlReadWrite
' Application.DisplayAlerts = True
End Sub
It unfortunately comes up with a pop up error that says:

"Assumptions.xls" is locked for editing

by 'another user'

Click 'Notify' to open a read-only copy of the document and receive notification of the document when it is no longer in use.

Is there a way around this problem, I would like to make the change and save over the existing read-only file with the modifications made in the code. I don't mind saving over the existing version once the changes are made, for example:

1. Open read-only workbook
2. Make changes
3. Save as a dummy workbook e.g. Assumptions_modified.xls in the same directory.
4. Close and delete the original Assumptions.xls in the same code (the file we opened in step 1.)
5. Rename Assumptions_modified.xls

Is this possible, or more conveniently changing the read-only status of the existing file.

Any help appreciated.

xluser2007
11-25-2008, 11:29 PM
Got it!



Option Explicit

Sub test()
Dim wb As Workbook
SetAttr "Read Only WB open test.xls", vbNormal
Set wb = Workbooks.Open("Read Only WB open test.xls", UpdateLinks:=0)
wb.Sheets(1).Range("A1").Activate
ActiveCell.Value = 1
wb.Close SaveChanges:=True
End Sub
You have to set the file attribute to readonly BEFORE OPENING the workbook.

Once you do this you can open the workbook without read-only and make changes and save (as a read-only if you like).

This could have only be achieved thanks to Ischyros pointing me in the right track with SetAttr method and this helpful (http://www.mrexcel.com/forum/showthread.php?t=80796) thread search at MrExcel.com.

thanks,