时间戳

首页 > 解决方案 > 绕过损坏文件上的“运行时错误 1004”

excel - 绕过损坏文件上的“运行时错误 1004”

问题描述

我有一个循环，它从工作表的列表中打开多个文件，然后复制数据，其中一些文件已被删除，但我可以排除这些（运行时错误 1004）。然而，一些文件已损坏，错误处理程序正在崩溃。同样的错误，不同的原因。知道如何排除这些吗？这些数据是无用的，所以需要跳过。

Sub EDITING LOOP()

Application.DisplayAlerts = False
Application.EnableEvents = False

Dim i As Long

    With Workbooks("BOOK1.xlsb").Worksheets("SHEET1")

     For i = 2 To 29

        xfile = .Cells(i, 1)
        On Error GoTo ErrHandler

        Workbooks.Open (xfile)
        Application.Goto Reference:="TABLE1"
        Selection.copy
        ActiveWindow.Close

ErrHandler:

        If Err.Number <> 0 Then .Cells(i, 2) = "ERROR" Else .Cells(i, 2) = "OK"
        .Cells(i, 3) = Err.Number

Err.Clear      
' On Error Resume Next (TRIED BUT DIDN'T WORK) 
' On Error GoTo 0      (TRIED BUT DIDN'T WORK)

        Next

    End With

Application.ScreenUpdating = True
Application.EnableEvents = True

End Sub

标签： excelvba

解决方案

将错误测试封装在自己的函数中通常会更好。通过这种方式，您可以更轻松地将“快乐路径”与“错误路径”分开。

Sub EDITINGLOOP()

    Application.DisplayAlerts = False
    Application.EnableEvents = False

    Dim i As Long

    With Workbooks("BOOK1.xlsb").Worksheets("SHEET1")

        For i = 2 To 29

            xfile = .Cells(i, 1)
            Dim myErrorNUmber As Long
            Dim myWorkbook As Excel.Workbook
            If Not TryOpenFile(xfile, myErrorNUmber, myWorkbook) Then

                .Cells(i, 2) = "ERROR"
                .Cells(i, 3) = myErrorNumber

            Else

                .Cells(i, 2) = "OK"
                Application.GoTo Reference:="TABLE1"
                Selection.Copy
                ActiveWindow.Close

            End If

        Next

    End With

    Application.ScreenUpdating = True
    Application.EnableEvents = True

End Sub


Public Function TryOpenFile(ByVal ipFileName As String, ByRef opErrorNum As Long, ByRef opWorkBook As Excel.Workbook) As Boolean

    On Error Resume Next
    Set opWorkBook = Workbooks.Open(xfile)
    opErrorNum = Err.Number
    TryOpenFile = Err.Number = 0
    On Error GoTo 0

End Function

您会注意到 tryOpenFile 函数返回错误号和对工作簿的引用（如果它已成功打开）。在打开工作表功能之后，您当前的代码似乎对活动工作表做出了一些不确定的假设。我没有尝试更正您的代码。

此处更详细地讨论了使用 On Error Resume Next 来管理错误条件的测试 https://rubberduckvba.wordpress.com/2019/05/

推荐阅读