如何从 VBA 在 Excel 2010 中创建安全受密码保护的 .xls 文件

发布于 2024-10-18 08:26:16 字数 1184 浏览 6 评论 0原文

我发现在 Excel 2010（和 2007）中，当您尝试选择一种更安全的加密类型时，它似乎会默默地忽略您的设置，给您留下一个可以在几秒钟内破解的文件 decryptum 例如。

这不适用于默认情况下 AES 加密的新文件格式，仅当您保存为 97-2003 格式时。

这是我在工作簿中创建的宏：

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

通过省略 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 然后无法像我预期的那样解密。

我做错了什么还是这是一个错误？如果这是一个错误，我该如何解决它？

我在下面添加了适用于 Excel 2010 的某种解决方案，但我希望有一些更简单的解决方案，并且最好也适用于 2007 年。

原文

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, _
    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.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

A君 2024-10-25 08:26:16

这是因为 Office 2007/2010 未启用 RC4 - 您必须手动执行与管理模板相同的操作。

为了证明这一点，在 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

您可能会看到 1) Office Standard、2) 错误、3) 40 和 4) 办公室。这意味着 RC4 没有被使用；加密默认为标准和糟糕/旧的 Excel 加密。显然，MSFT 也认为 RC4 非常糟糕，因此在较新的 Office 客户端中使用它有点困难。

请参阅这两篇文章：

脚注：SaveAs 例程的FileFormat 部分不是这里的罪魁祸首。它只是按照 CryptoAPI 的指示去做。事实上，从 Excel 2010 VBA 开始，您可以使用 Excel 2003 的 SaveAs，它略有不同，但不会产生任何影响（从 Excel 2010 开始，它是一个隐藏例程，可以像 >ActiveWorkbook.[_SaveAs] 文件名：=“加密_test.xls”，密码：=“密码”。）

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:

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".)

回复收藏 0 原文