时间戳

首页 > 解决方案 > 双重错误处理 - 错误后继续而不返回

excel - 双重错误处理 - 错误后继续而不返回

问题描述

我有一堆 excel 文件，其中一些包含 P3 表，一些 P2，并且很少。我尝试使用错误处理，但如果连续两个文件没有 P3. 我在错误块中有一个错误。所以我试图用 resume next 或其他东西来结束错误块。但后来我会回到错误之后。虽然我实际上想在错误处理块之后继续（不结束代码）。有什么建议么？或者使用不同的方法来识别工作表的存在会更好吗？

Do While myFile <> ""
      Set wbCase = Workbooks.Open(Filename:=myPath & myFile, UpdateLinks:=0)
On Error GoTo Line2
Set wsCaseinfo = wbC.Worksheets("P3")
GoTo Line3:
Line2:
On Error GoTo Line0
Set wsCaseinfo = wbC.Worksheets("P2")
Resume Next
Line3:
On Error GoTo 0

...
some code doing stuff
...

On Error Resume Next
k = k + 1
wbNew.Sheets("Sheet1").Cells(k, 1) = wbC.Name
wbNew.Sheets("Sheet1").Cells(k, 2) = wsCinfo.Name

GoTo Line1

Line0:
wbNew.Sheets("Sheet1").Cells(k, 3) = "Fail"
Resume Next

Line1:

wbC.Close SaveChanges:=False     

'Get next file name

     myFile = Dir

  Loop

标签： excelvba

解决方案

你可以这样做：

Option Explicit

Sub Tester()

    Dim myFile, myPath, ws As Worksheet, wbCase As Workbook, sheetName

    myPath = "path/to/files/"

    myFile = Dir(myPath & "*.xls*")

    Do While myFile <> ""

        Set wbCase = Workbooks.Open(Filename:=myPath & myFile, UpdateLinks:=0)
        For Each ws In wbCase.Worksheets
            Select Case ws.Name
                Case "P3", "P2":
                    'got a match, so do something here with ws
                    Exit For 'umless you want to look for other sheets

            End Select
        Next ws

        wbCase.Close SaveChanges:=False
        myFile = Dir
    Loop

End Sub

推荐阅读