PDA

View Full Version : Solved: Save file and a standalone read-only version



sassora
07-04-2012, 07:00 AM
Hi all

I have a workbook that I need to save using VBA in two ways. The first is the default type of save, the second is to save a read only copy of the file but in a different location.

The VBA code I am looking for would do something like:
1) save the current file
2) save a read only version

but after the first use this would become
1) save the current file
2) Switch off the read-only in the other closed file
2) Save the contents of the current file over it
4) Reapply the read-only

Is this something you could help with?

sassora
07-04-2012, 07:29 AM
This takes me quite far.

How do I remove the option of the file being read-only when it opens?

Application.DisplayAlerts = False
ChDir "C:\Documents and Settings\sassora\Desktop"
ActiveWorkbook.SaveAs Filename:= _
"C:\Documents and Settings\sassora\Desktop\testdoc.xls", FileFormat:=xlNormal _
, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
CreateBackup:=False


ActiveWorkbook.SaveAs Filename:= _
"C:\Documents and Settings\sassora\Desktop\testdoc2.xls", FileFormat:=xlNormal _
, Password:="", WriteResPassword:="", ReadOnlyRecommended:=True, _
CreateBackup:=False

Application.DisplayAlerts = True

sassora
07-04-2012, 07:53 AM
Is this a better way of making the file read only, I am using xl2003 and xl2010.

http://www.mrexcel.com/forum/showthread.php?251437-VBA-to-Save-file-as-Read-Only

sassora
07-04-2012, 01:21 PM
After some dabbling, the following seems to do the trick.

Sub SaveFiles()

Const PathWorkList As String = "F:\fun.xls"
Const PathWorkListReadOnly As String = "F:\fun (for browsing).xls"

Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:=PathWorkList

On Error Resume Next
VBA.SetAttr PathWorkListReadOnly, vbNormal

ActiveWorkbook.SaveAs Filename:=PathWorkListReadOnly
ActiveWorkbook.SaveAs Filename:=PathWorkList

VBA.SetAttr PathWorkListReadOnly, vbReadOnly

Application.DisplayAlerts = True

End Sub