Change Read/Write attribs with VBA

[jfarc]

The following is what I need to do to a currently closed(not open) .xls file.

From a separate open .xls file I need a VBA command that will change the File/Properties/Attributes from 'Read-Only' to allow Read/Write.

Then I will open this .xls file, make my needed changes, then Save it. Then I need to re-check the 'Read-Only' box within the Properties/Attributes window.

Thanks in advance. . .

 

[pgc01]

Hi jfarc

You can use the Attributes property of the File object to set/reset attributes.

Ex: this code sets/resets an attribute in a closed file:

Sub SetResetAttr(sFilePathName As String, bytAttr As Byte, bSet As Boolean)
 
With CreateObject("Scripting.FileSystemObject").GetFile(sFilePathName)
    If bSet Then
        .Attributes = .Attributes Or bytAttr
    Else
        .Attributes = .Attributes And Not bytAttr
    End If
End With
End Sub

Ex: To reset the ReadOnly attribute (code 1) in the closed file c:\tmp\attr.xlsm

Sub SetResetAttrTest()
    
    SetResetAttr "c:\tmp\attr.xlsm", 1, False    ' 1 is the code for ReadOnly
End Sub

Remarks:

1 - You can get the codes for the other file attributes in the help. In the later versions of excel you can even use constants directly, like ReadOnly instead of 1, which makes the code easier to read.

2 - In this example I didn't use error handling, I assumed the file exists, etc. You can add it if you want.

 

[jfarc]

That worked beautifully, thanks!