VBA Excel Encription 1

[EriRobert]

Hello All

oExcelWSheet.SaveAs "C:\abc.xls", , "pass123"

This will save an excel document with a password. How can you also instruct the save to add encryption (e.g. RC4, Microsoft Enhanced Cryptographic Provider v1.0).

Easy, manually through Excel itself, but through VBA, how? I was hoping for something in the saveas method above, but no.

Any ideas?

Many Thanks

 

[combo]

Before SaveAs method, use:

oExcelWSheet.SetPasswordEncryptionOptions ' set arguments here

combo

 

[EriRobert]

oExcelWBook.SetPasswordEncryptionOptions _
        PasswordEncryptionProvider:="RC4, Microsoft RSA SChannel Cryptographic Provider", _
        PasswordEncryptionAlgorithm:="RC4", _
        PasswordEncryptionKeyLength:=128, _
        PasswordEncryptionFileProperties:=True
oExcelWSheet.SaveAs "C:\abc.xls", , "pass123"

Thanks for the solution combo.

This has got me 90% there. The method is related to a workbook rather than a worksheet, but adding where you suggest gives me the error "Invalid procedure call or argument". I'm sure I have this encryption type on my machine as I can see it when I encrypt an excel sheet manually. I've tried varying the position of the .SetPasswordEncryptionOptions (after save etc.) but I don't think this is the problem.

Regards

 

[combo]

What kind of protection do you mean? With Tools>Protection worksheet/workbook (windows) can only be protected against changes, this is simple hashing encryption and cannot be changed. SaveAs method applies to protection against opening, and applies for a workbook only.

What is oExcelWBook? You can apply SaveAs method either for workbook or worksheet. I assumed that it is workbook.
If you proceed with worksheet that is saved as separate workbook, save it, reopen as workbook and set encryption options, and save again with password. Another option is to use Worksheet.Copy method, that creates new workbook, and next proceed with the new workbook.

combo

 

[EriRobert]

combo, sorry for confusing you. I do mean encryption rather than protection. Anyway I've got to the bottom of it now - the only problem with the code above is that I had the provider name wrong - it should be 'Microsoft RSA SChannel Cryptographic Provider' - lose the initial RC4.

Thanks for you help combo - star-time