Set Read Only

[Peter Andrews]

I automatically save a copy of a file to a communal location (Y drive), so
that other persons may read it, using the following code:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
'save copy to Network Storage Folder (Y Drive)
ActiveWorkbook.SaveCopyAs "Y:\" & ActiveWorkbook.Name
End Sub

This works fine but how do I make this copy a read only file?

Peter

 

[Dave Peterson]

You can use SetAttr to change the file to readonly:

Dim myNewName As String

myNewName = "Y:\" & ActiveWorkbook.Name

On Error Resume Next 'in case there isn't one there
SetAttr pathname:=myNewName, attributes:=vbNormal
On Error GoTo 0

ActiveWorkbook.SaveCopyAs myNewName
SetAttr pathname:=myNewName, attributes:=vbReadOnly

 

[Peter Andrews]

You can use SetAttr to change the file to readonly:

Dim myNewName As String

myNewName = "Y:\" & ActiveWorkbook.Name

On Error Resume Next 'in case there isn't one there
SetAttr pathname:=myNewName, attributes:=vbNormal
On Error GoTo 0

ActiveWorkbook.SaveCopyAs myNewName
SetAttr pathname:=myNewName, attributes:=vbReadOnly

Thank you for your solution - however it has highlighted something that has
nothing to do with Excel. If I copy a read only file to my (new and first)
NAS drive the read only attribute is cleared and I cannot set it even by
using the file properties window - some further investigation is required.
Thank you for your time.

Peter