如何从 VBA 在 Excel 2010 中创建受密码保护的安全 .xls 文件
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5079342/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How do I create securely password protected .xls files in Excel 2010 from VBA
提问by Todd Main
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 decryptumfor example.
我发现在 Excel 2010（和 2007）中，当您尝试选择一种更安全的加密类型时，它似乎会默默地忽略您的设置，从而为您留下一个可以在解密时在几秒钟内破解的文件。
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.
这不适用于默认情况下 AES 加密的新文件格式，仅当您保存为 97-2003 格式时。
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:
通过省略 FileFormat 参数，很容易让 Excel 2003（正确）使用这些加密设置输出：
Sub enc()
Me.SetPasswordEncryptionOptions _
PasswordEncryptionProvider:= "Microsoft Strong Cryptographic Provider", _
PasswordEncryptionAlgorithm:= "RC4", _
PasswordEncryptionKeyLength:=128, _
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.
我在下面为 Excel 2010 添加了各种解决方案，但我希望有一些更简单的解决方案，并且最好也适用于 2007 年。
采纳答案by Todd Main
A workaround for Office 2010:
Office 2010 的解决方法：
- add the office 2010 administrative template (office14.adm)
- 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)
- 添加 office 2010 管理模板 (office14.adm)
- 将“用户配置\管理模板\Microsoft Office 2010\安全设置\受密码保护的 Office 97-2003 文件的加密类型”编辑为“Microsoft Strong Cryptographic Provider,RC4,128”（不带引号）
回答by Todd Main
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.
这是因为 RC4 未在 Office 2007/2010 中启用 - 您必须手动执行与管理模板完全相同的操作。
To prove this point, in Excel 2010, do your routine above and then open your "encryption_test.xls" file and press Alt+ F11to go to the VBE. Then run the following (press Ctrl+ Gto open the Immediate window first):
为了证明这一点，在 Excel 2010 中，执行上面的例程，然后打开“encryption_test.xls”文件并按Alt+F11转到 VBE。然后运行以下命令（先按Ctrl+G打开立即窗口）：
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.
您可能会看到 1) Office Standard、2) False、3) 40和 4) Office 的值。这意味着没有使用 RC4；加密默认为标准 - 和糟糕/旧 - Excel 加密。显然，MSFT 也认为 RC4 非常糟糕，因此在较新的 Office 客户端中使用它有点受阻。
See these two articles:
看这两篇文章：
Footnote: The FileFormatportion of the SaveAsroutine 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".)
脚注：例程的FileFormat部分SaveAs不是这里的罪魁祸首。它只做 CryptoAPI 告诉它做的事情。实际上，从 Excel 2010 VBA 开始，您可以使用 Excel 2003 的SaveAs，它略有不同，并且没有任何区别（从 Excel 2010 开始，它是一个隐藏的例程，可以像ActiveWorkbook.[_SaveAs] Filename:="encryption_test.xls", Password:="password".）
