Workbook Open ReadOnly

Hi y'all,

Situation
I have a Excel Report publication system where the "Master-Report" Saves copies of itself. From the Master-Report I can then publish the Reports by Copy-Paste the copies to a public server.

On our Intranet I have HTML links to the files on the public server. The reports always has the same name on the server. This way i can overwrite the old reports and the HTML links does not have to be changed.

Workflow:

Local drive: Master Report.xlsm --> Local drive: Report DK Jan 11 --> Public server: Report DK <-- Intranet:HTML Link

When publishing the reports to the public server, I use this code:

FileCopy "Source", "Target"

Problem
I want the published reports to be opened in ReadOnly, this way readers cannot write in the reports, and I can allways replace the reports with new reports, even if they are opened by other users.
But if I make the files ReadOnly I cannot overwrite them with new publications, I will have to delete the old reports manually before publishing the new ones.
How can I solve this Dilemma?

Possible solutions
1. In VBA I have a code which automatically makes the Excel reports ReadOnly. Something like:

Private sub workbook_Open()
activeworkbook.ReadOnly=True
end sub

Does such a code exist?

2. If it is possible to implement some kind of feature into the HTML Link which forces the reports to open in ReadOnly mode.


Is this clear? Do you guys have any alternative solutions? Or know how to create one of the solutions mentioned above?

Kind Regards
KRAND

Hi,
You could try
ActiveWorkbook.ChangeFileAccess Mode:=xlreadonly
ActiveWorkbook.ChangeFileAccess Mode:=xlreadwrite

or you could use the filesystemobyect on a colsed file - attribute 1 for readonly or 0 for write
so c:file.ext,1,false

hope this helps

Hi D_Rennie

I have tried to use the file system object, but i do not know how. could you give an example?

Sub ReadOnly()
Dim FSO As Scripting.FileSystemObject
Dim File As Scripting.File

Set FSO = New Scripting.FileSystemObject
Set File = FSO.GetFile(DKXL & "Performance Report DK.xlsm")

'set Read-Only attribute


Set File = Nothing
Set FSO = Nothing

End Sub

Kind Regards
KRAND

glad you got this sorted and am happy with it, honestly i forgot about this thread.