vba 打开excel文件时出现运行时错误

Run-time error upon opening excel file

提问by danielle

I'm trying to open excel files in a button_clickevent. I don't encounter any errors with the first four excel files i opened, but as my macro open the fifth one, it stops and shows this run-time error:

我正在尝试在button_click活动中打开 excel 文件。我打开的前四个 excel 文件没有遇到任何错误，但是当我的宏打开第五个文件时，它停止并显示此运行时错误：

Run-time error '-2147021892 (80070bbc)':

office has detected a problem with this file. 
To help protect your computer this file cannot be opened.

Here's my code for opening excel files:

这是我打开excel文件的代码：

    Set wb = Workbooks.Open(fileName:=fileName, UpdateLinks:=True)

回答by blablubbb

I had the same problem. File was corrupted and VBA open threw that error. As a possible solution I found this (faname is a string with the path):

我有同样的问题。文件已损坏，VBA 打开引发了该错误。作为一个可能的解决方案，我发现了这个（faname 是一个带有路径的字符串）：

Workbooks.Open FileName:= fname, UpdateLinks:=False, ReadOnly:=True, _
   IgnoreReadOnlyRecommended:=True, Password:="", Editable:=FALSE, _
   CorruptLoad:= xlExtractData

The important part is "CorruptLoad:= xlExtractData", that makes it possible to load the data from corrupted files without throwing this error. The other things are just there to prevent the file from doing something... together with

重要的部分是“CorruptLoad:= xlExtractData”，它可以从损坏的文件中加载数据而不会引发此错误。其他事情只是为了防止文件做某事......连同

Application.DisplayAlerts = False
Application.AskToUpdateLinks = False
Application.EnableEvents = False
Application.AutomationSecurity = msoAutomationSecurityForceDisable
Application.Calculation = xlCalculationManual

Just as a precaution before opening the file... if you do that do not forget to undo it before your Macro finishes like (these are my standard settings, use your own! you may find them out using Debug.Print in the Immediate Window):

作为打开文件之前的预防措施...如果您这样做，请不要忘记在宏完成之前撤消它（这些是我的标准设置，请使用您自己的设置！您可能会在即时窗口中使用 Debug.Print 找到它们)：

Application.DisplayAlerts = True
Application.AskToUpdateLinks = True
Application.EnableEvents = True
Application.Calculation = xlNormal
Application.AutomationSecurity = msoAutomationSecurityLow

Be careful what your security settings actually are and do not blindly copy these changes of settings... Also best to catch errors ("On Error ...") and terminate with resetting your previous settings.

小心你的安全设置实际上是什么，不要盲目地复制这些设置的更改......也最好捕捉错误（“On Error ...”）并以重置你以前的设置终止。

回答by jordanhill123

Possible resolutions

可能的解决方案

Microsoft Support - Error message in Office when a file is blocked by registry policy settingsgives several mechanisms for how to possibly bypass this error if you trust the document content.

Microsoft 支持 - 当文件被注册表策略设置阻止时，Office 中的错误消息提供了几种机制，如果您信任文档内容，如何可能绕过此错误。

Some Key Notes:

一些关键注意事项：

For Excel 2010 or 2013

Change the File Block settings to disable the restriction of certain file types through File -> Options -> Trust Center -> Trust Settings

For Excel 2003 or 2007

You have to change the value of the FileOpenBlock registry subkey to disable the restriction of certain file types. This is located at

For Excel 2007:

HKEY_CURRENT_USER\Software\Policies\Microsoft\Office\12.0\Excel\Security\FileOpenBlock

For Excel 2003:

HKEY_CURRENT_USER\Software\Policies\Microsoft\Office\11.0\Excel\Security\FileOpenBlock

Subkeys should be as follows:

对于 Excel 2010 或 2013

通过文件 -> 选项 -> 信任中心 -> 信任设置更改文件阻止设置以禁用某些文件类型的限制

对于 Excel 2003 或 2007

您必须更改 FileOpenBlock 注册表子项的值以禁用对某些文件类型的限制。这位于

对于 Excel 2007：

HKEY_CURRENT_USER\Software\Policies\Microsoft\Office\12.0\Excel\Security\FileOpenBlock

对于 Excel 2003：

HKEY_CURRENT_USER\Software\Policies\Microsoft\Office\11.0\Excel\Security\FileOpenBlock

子键应如下所示：

Why this is occurring

为什么会发生这种情况

From Microsoft Support:

来自微软支持：

SYMPTOMS

You perform one of the following actions in a Microsoft Office 2010 application:

?Open an embedded object

?Perform a mail merge

?Open a file from a viewer

In this situation, you receive the following error message:

Office has detected a problem with this file. To help protect your computer this file cannot be opened.

CAUSE

This problem occurs because Office File Validation detects a problem with the file that may pose a security risk. You receive the error message for a malicious file or for a damaged file.

症状

您在 Microsoft Office 2010 应用程序中执行以下操作之一：

?打开一个嵌入对象

?执行邮件合并

?从查看器打开文件

在这种情况下，您会收到以下错误消息：

Office 检测到此文件有问题。为了帮助保护您的计算机，无法打开此文件。

原因

出现此问题的原因是 Office 文件验证检测到可能造成安全风险的文件问题。您会收到有关恶意文件或损坏文件的错误消息。

It appears that Office is detecting something possibly malicious with the file, such as a virus or other malware, or it's just possible that the file is corrupted. If you trust this document, proceed with opening it. Otherwise be extremely cautious to avoid an malware infection of some type.

Office 似乎正在检测文件中可能存在恶意的内容，例如病毒或其他恶意软件，或者文件可能已损坏。如果您信任此文档，请继续打开它。否则要格外小心，以避免某种类型的恶意软件感染。

回答by shopkins

I tried the line of code above with my "corrupted" file. The result was disasterous, (but original was backed up). All Excel worksheets (13) now have text, and All approx 93 pages of VBA code are gone. Styles=17: File size is <2000kb.

我用我的“损坏的”文件尝试了上面的代码行。结果是灾难性的，（但原件被备份）。所有 Excel 工作表 (13) 现在都有文本，并且所有大约 93 页的 VBA 代码都消失了。Styles=17：文件大小 <2000kb。

One cell with a changed cell format causes the infamous M/S error text. I don't think this is corrupt, but it may be too much programming for Excel(s).

具有更改单元格格式的一个单元格会导致臭名昭著的 M/S 错误文本。我不认为这是损坏的，但它可能是 Excel(s) 的太多编程。

回答by Kevin

Possibly the file seems to downloaded/copied from external source, like the internet. The below page discussed how to "unblock" such files programatically via VBA. https://answers.microsoft.com/en-us/msoffice/forum/msoffice_excel-msoffice_custom/how-to-unblock-file-using-vba/bed82938-6a57-403c-afcf-fa76a26a1ac6

可能该文件似乎是从外部来源（如互联网）下载/复制的。下面的页面讨论了如何通过 VBA 以编程方式“取消阻止”此类文件。 https://answers.microsoft.com/en-us/msoffice/forum/msoffice_excel-msoffice_custom/how-to-unblock-file-using-vba/bed82938-6a57-403c-afcf-fa76a26a1ac6

See Andreas Killer's solution. he mentioned that what you clear is not a file attribute, you remove the alternate data stream "Zone.Identifier" from the file. And gives the following links:- A wiki link ... en.wikipedia.org/wiki/NTFS#Alternate_data_streams_.28ADS.29 http://vb.mvps.org/samples/Streams/

请参阅 Andreas Killer 的解决方案。他提到您清除的不是文件属性，而是从文件中删除备用数据流“Zone.Identifier”。并提供以下链接：- 维基链接... en.wikipedia.org/wiki/NTFS#Alternate_data_streams_.28ADS.29 http://vb.mvps.org/samples/Streams/

Above 2nd link to Karl E. Peterson's website provides a Streams.zip file, which contains CStreams class that needs to be imported into your project and use the KillStream function.

Karl E. Peterson 网站的第二个链接提供了一个 Streams.zip 文件，其中包含需要导入到您的项目中并使用 KillStream 函数的 CStreams 类。

Sub Test()
  Dim C As New CStreams
  Dim i As Integer
With C
  .FileName = "C:\test.txt"
  For i = 1 To .Count - 1
    Debug.Print .KillStream(i)
  Next
End With
End Sub

-Credit to Andreas Killer

- 归功于 Andreas Killer

Hope this helps.

希望这可以帮助。