How do I create securely password protected .xls files in Excel 2010 from VBA

I found that in Excel 2010 (and 2007), when you try and choose one of the more secure types of encryption, it seems to silently ignore your settings, leaving you with a file that can be cracked in seconds at decryptum for example.

This does not apply to the new file formats that are AES encrypted by default, only when you are saving down to the 97-2003 format.

This is the macro I create in the workbook:

Sub enc()
  Me.SetPasswordEncryptionOptions _
    PasswordEncryptionProvider:= "Microsoft Strong Cryptographic Provider", _
    PasswordEncryptionAlgorithm:= "RC4", _
    PasswordEncryptionKeyLength:=128, _
    PasswordEncryptionFileProperties:=True
  Me.SaveAs _
    Filename:="encryption_test.xls", _
    FileFormat:=xlExcel8, _
    Password:="password"
End Sub

It is quite easy to get Excel 2003 to (correctly) output with these encryption settings by omitting the FileFormat parameter:

Sub enc()
  Me.SetPasswordEncryptionOptions _
    PasswordEncryptionProvider:= "Microsoft Strong Cryptographic Provider", _
    PasswordEncryptionAlgorithm:= "RC4", _
    PasswordEncryptionKeyLength:=128, 开发者_JAVA技巧_
    PasswordEncryptionFileProperties:=True
  Me.SaveAs _
    Filename:="encryption_test.xls", _
    Password:="password"
End Sub

decryptum then fails to decrypt as I would expect.

Am I doing something wrong or is this a bug? If it is a bug, how do I work around it?

I've added a solution of sorts for Excel 2010 below, but I'm hoping for something simpler, and preferably something that works for 2007 as well.

It's because RC4 isn't enabled with Office 2007/2010 - you'll have to manually do exactly what you are doing with the admin template.

To prove this point, in Excel 2010, do your routine above and then open your "encryption_test.xls" file and press Alt + F11 to go to the VBE. Then run the following (press Ctrl + G to open the Immediate window first):

Sub CheckEncryption()
    Dim aw As Workbook: Set aw = ActiveWorkbook
    With aw
        Debug.Print .PasswordEncryptionAlgorithm
        Debug.Print .PasswordEncryptionFileProperties
        Debug.Print .PasswordEncryptionKeyLength
        Debug.Print .PasswordEncryptionProvider
    End With
End Sub

What you'll probably see are the values of 1) Office Standard, 2) False, 3) 40, and 4) Office. What this means is that as RC4 isn't being used; encryption defaults to standard - and crummy/old - Excel encryption. Obviously RC4 is thought of as pretty crummy too by MSFT, so use of it has been a bit hobbled in the newer Office clients.

See these two articles:

• Plan cryptography and encryption settings for Office 2010

• Plan document protection settings in the 2007 Office system

Footnote: The FileFormat portion of the SaveAs routine isn't culprit here. It's only doing what the CryptoAPI is telling it to do. In fact, from Excel 2010 VBA, you could use Excel 2003's SaveAs, which is slightly different and it wouldn't make a difference (from Excel 2010, it's a hidden routine that can be accessed like ActiveWorkbook.[_SaveAs] Filename:="encryption_test.xls", Password:="password".)

A workaround for Office 2010:

1. add the office 2010 administrative template (office14.adm)
2. edit "User Configuration\Administrative Templates\Microsoft Office 2010\Security Settings\Encryption type for password protected Office 97-2003 files" to "Microsoft Strong Cryptographic Provider,RC4,128" (without the quotes)