Set Read Only property in Excel from Access VBA

[Manuel]

I have a database that transfers two tables to a spreadsheet using the
DoCmd.TransferSpreadsheet method (the first DoCmd.TransferSpreadsheet creates
the Excel file and outputs the data from the first table into a sheet and the
second DoCmd.TransferSpreadsheet outputs the second table to another sheet in
this same speadsheet).

I need to have this newly created Excel file set to Read Only. How do I set
the Read Only property using VBA (is it possible to do this through VBA)?

Thanks,

Manuel

 

[kc-mass]

This air code will let you save your file as read only and it is password
protected
for the read only attribute. If you want someone to modify it send them the
password.

Sub WorkBook_RO(FileToRead)
Dim strFileName As String
Dim XlApp As Excel.Application
Dim xlBook1 As Excel.Workbook
strFileName = FileToRead
Set XlApp = Excel.Application
Set xlBook1 = XlApp.Workbooks.Open(strFileName)
XLBook1.WritePassword = "HenryII"
XLAppX.Book1.Close savechanges:=True
XlApp.Quit
Set XLApp = Nothing
End Sub

Regards

Kevin

 

[Manuel]

Awesome, thanks!

FYI: I received an error message (438: Object does not support this object
or method) at the commented line below, so I had to revise the code a bit.
But otherwise it worked perfectly. Thanks again!!

Sub WorkBook_RO(FileToRead)
Dim strFileName As String
Dim XlApp As Excel.Application
Dim xlBook1 As Excel.Workbook
strFileName = FileToRead
Set XlApp = Excel.Application
Set xlBook1 = XlApp.Workbooks.Open(strFileName)
xlBook1.WritePassword = "HenryII"
'XlApp.Book1.Close savechanges:=True
XlApp.ActiveWorkbook.Save
XlApp.Quit
Set XlApp = Nothing
End Sub