Change Read/Write attribs with VBA

J

jfarc

Active Member
Joined
Mar 30, 2007
Messages
316
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. . .
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
Sort by date Sort by votes
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:

Code: 
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

Code: 
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.
 
Upvote 0
You must log in or register to reply here.

Similar threads

I
  • Question Question
Unable to Open or Copy Corrupted Excel Files on Network Drive
Replies
1
Views
84
Candyman8019
C
S
  • Question Question
Is there a way to open a workbook, refresh everything and close after success.
Replies
1
Views
530
severynm
severynm
B
  • Solved
VBA to read 1 line at a time from 2 CSVs and write them sequentially to the active workbook
Replies
8
Views
428
BakerSteve
B
F
  • Solved
Read stored variable in "BuiltinDocumentProperties"
Replies
2
Views
336
Formula11
F
S
  • Question Question
Script to loop thru a number of folders and pulling particular cells into a master sheet
Replies
3
Views
412
santa12345
S

Forum statistics

Threads
1,222,647
Messages
6,167,329
Members
452,110
Latest member
eui

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top