尝试打开工作簿时的 VBA 错误处理

我试图循环浏览文件夹中的所有文件，打开它们并删除文档信息。我在处理无法打开的文件或打开时出现有关禁用宏的弹出消息时遇到问题。我尝试使用错误恢复下一步和错误转到 0 来解决此问题。但随后我遇到运行时失败，因为当我尝试关闭已打开的文件时，我的工作簿对象（wb）尚未设置。

我已阅读有关“On Error Resume Next”和“On error goto 0”的文档，但我不相信我在这里正确使用它们。 非常感谢任何帮助，谢谢。

Option Explicit
Sub test_Scrubber_New()

Dim directory As String, fileName As String, i As Variant, wb As Workbook
Application.DisplayAlerts = False
Application.ScreenUpdating = False

'directory = "C:\Users\bayli\Desktop\Files for Testing\"
directory = "C:\Users\bayli\Desktop\excel files\"
fileName = Dir(directory & "*.xl??")

i = 0
Do While fileName <> ""
    On Error Resume Next
    Set wb = Workbooks.Open(directory & fileName)
    On Error GoTo 0
        'remove info
        ActiveWorkbook.RemoveDocumentInformation (xlRDIAll)
    wb.Close True
    i = i + 1
    fileName = Dir()
    Application.StatusBar = "Files Completed:  " & i
Loop

Application.StatusBar = False
Application.ScreenUpdating = True
Application.DisplayAlerts = True
MsgBox "Complete"

End Sub

我更新了我的代码以包括： If Not wb Is Nothing then按照@PatricK的建议删除信息，它正在工作，但是它不断停止并弹出有关更新链接的弹出窗口。如果我单击“不更新”，我的代码将继续根据需要工作，但有没有办法处理这个问题。我正在循环遍历超过 5k 个文件，所以你可以想象这需要一段时间。所花费的时间不是问题，但目前我坐在这里不得不多次单击“不更新”。我认为 Application.DisplayAlerts = False 会阻止这些弹出窗口，但事实并非如此。

好的，这里有几个问题。首先，关于错误处理。当您使用内联错误处理时（On Error Resume Next），基本模式是关闭自动错误处理，运行您想要“捕获”错误的代码行，然后测试看看是否Err.Number为零：

On Error Resume Next
ProcedureThatCanError
If Err.Number <> 0 Then
    'handle it.
End If
On Error GoTo 0

其余问题涉及打开工作簿时可能遇到的对话框。其中大部分记录在MSDN 页面 https://learn.microsoft.com/en-us/office/vba/api/excel.workbooks.open for Workbook.Open，但你会想要改变Application.AutomationSecurity属性来根据需要处理宏提示。对于更新，您应该传递适当的UpdateLinks范围。我还建议指定IgnoreReadOnlyRecommended, Notify, and CorruptLoad。像这样的东西应该有效（未经测试），或者至少让你更接近：

Sub TestScrubberNew() 'Underscores should be avoided in method names.

    Dim directory As String, fileName As String, i As Variant, wb As Workbook
    Application.DisplayAlerts = False
    Application.ScreenUpdating = False

    Dim security As MsoAutomationSecurity
    security = Application.AutomationSecurity
    Application.AutomationSecurity = msoAutomationSecurityForceDisable

    directory = "C:\Users\bayli\Desktop\excel files\"
    fileName = Dir(directory & "*.xl??")

    i = 0
    Do While fileName <> vbNullString
        On Error Resume Next
        Set wb = Workbooks.Open(fileName:=directory & fileName, _
                                UpdateLinks:=0, _
                                IgnoreReadOnlyRecommended:=True, _
                                Notify:=False, _
                                CorruptLoad:=xlNormalLoad)
        If Err.Number = 0 And Not wb Is Nothing Then
            On Error GoTo 0
            wb.RemoveDocumentInformation xlRDIAll
            wb.Close True
            i = i + 1
            Application.StatusBar = "Files Completed:  " & i
            fileName = Dir()
        Else
            Err.Clear
            On Error GoTo 0
            'Handle (maybe log?) file that didn't open.
        End If
    Loop

    Application.AutomationSecurity = security
    Application.StatusBar = False
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
    MsgBox "Complete"

End Sub