I have a client request to return a password protected Excel file from an automated ETL process.
Does anyone know of a way to password protect an Excel file when Office is not installed? We don't install it on our production servers.
I can do this with the following script task from my development box, because I have Office installed:
Dim xlApp As Object
Dim xlWB As Object
xlApp = CreateObject("Excel.Application")
xlApp.Visible = False
Dim sSourceFile As String
sSourceFile = Variables.ExcelFileName
xlWB = xlApp.Workbooks.Open(sSourceFile)
xlWB.Password = Variables.EncryptionPassword
xlWB.SetPasswordEncryptionOptions( _
"Microsoft RSA SChannel Cryptographic Provider", _
"RC4", 128, True)
xlWB.Save()
xlWB.Close()
xlWB = Nothing
xlApp.Quit()
xlApp = Nothing
Dts.TaskResult = ScriptResults.Success