VBA for Saving Read Only File

[charllie]

Hi All
I have a file that is saved as read only using the below steps to achieve this.

1) Make the file/database READ ONLY when saving it
(Select Save As/Tools/Tick the Read Only Box)

2) Right click on file
Go to properties
Tick the read only box

The reason it is done this way is because the file will be shared. Also, step 2 ensures that the read only message does not appear when the file is opened (this avoids confusion for the operators using it).

How the file works is that the operator fills in certain bits of information in set cells and then hits a “Finish” button. The “Finish” button runs a macro which opens a CSV file in another location, finds the next empty row and then enters information completed by the operator and then saves/closes the CSV file.

What I would like to do is, when the CSV file has been saved/closed I would like to also copy the information to “sheet 2” of the Read Only workbook.
My question is, is it possible and if so does anyone have a code to perform the following:

Once the information has been copied to “sheet 2” of the Read Only file I would like to save the updates to the Read Only file and but have it saved as Read Only again.​

Is there a way of doing this and if so I would be really grateful if anyone had a some code out there to help me?

Thanks

 

[steve the fish]

Here is how you can open your read only file as a normal file and then close, save and reassign the read only attribute:

    myFile = "C:\Users\Steve\MyExcelFile.xlsx" 'put filepath here
    SetAttr myFile, vbNormal
    Set wb = Workbooks.Open(myFile)
    
    'do stuff
    
    wb.Close True 'close and save
    SetAttr myFile, vbReadOnly 'reassign read only

 

[charllie]

Hi Steve

Thanks for getting back to me, i really appreciate it.

How would i input this code if the file is already open. I suppose what i am trying to do is change the attribute whilst keeping the file open. Is that possible?

Thanks

Charllie

 

[steve the fish]

If it's open it's read only and you can't save it. You will need to change the attribute before opening it

 

[charllie]

Hi Steve

Thanks for the reply. Did a bit more searching and managed to find a solution.

The following code allows me to change the sttributes of the open read only file. run my code and make my changes etc. Save the workbook and then convert it back to read only.

Hope this helps someone else.

   ActiveWorkbook.ChangeFileAccess Mode:=xlReadWrite     

         MsgBox "Run My Code Here"    
    
         ThisWorkbook.Save  
    
    ActiveWorkbook.ChangeFileAccess Mode:=xlReadOnly

Thanks again for you suggestion and time, i really appreciate it.

charllie