Excel VBA - 在目录中循环文件时，如何跳到下一个文件 onError

Excel VBA - when looping through files in a directory, how to skip to next file onError

提问by Petrov

I'm looping through the files in a directory, where I need to open each file, do some manipulations and then close it.

我正在遍历目录中的文件，我需要在其中打开每个文件，进行一些操作，然后关闭它。

From time to time the Workbooks.Open fails with an error, and I simply want to display a MsgBox and then continue to the next file in the directory loop. But since the Error block is outside the loop, I can't just call 'Loop'... so how do I do this ?

Workbooks.Open 有时会因错误而失败，我只想显示一个 MsgBox，然后继续到目录循环中的下一个文件。但是由于 Error 块在循环之外，我不能只调用“Loop”...那么我该怎么做呢？

The test below immediately triggers an error that I can't call "Loop" without a "For"...

下面的测试立即触发一个错误，我不能在没有“For”的情况下调用“Loop”...

Sub test()
    Dim folderPath As String
    Dim filename As String

    folderPath = 'C:\myDirectory'
    filename = Dir(folderPath & "*.xlsx")

    Do While filename <> ""

        On Error GoTo OpenFailedError
            Set wb = Workbooks.Open(folderPath & filename)
        On Error GoTo 0

        filename = Dir
    Loop

Exit Sub

OpenFailedError:
    MsgBox ("Unable to open file " & filenameString)
    Loop

End Sub

回答by Doug Glancy

This is untested, but better, I'm sure.

这是未经测试的，但更好，我敢肯定。

Note that you have a couple of undeclared variables: wband filenameString. I strongly recommend using Option Explicitat the top of each module to avoid undeclared or misnamed variables. In this case, it looks like filenameStringwas supposed to be FileName. Note also that I put some capitals in the variable names. This helps you notice when you mistype a name (using lower-case letters) as it will fail to resolve to upper-case.

请注意，您有几个未声明的变量：wb和filenameString。我强烈建议Option Explicit在每个模块的顶部使用，以避免未声明或错误命名的变量。在这种情况下，它看起来filenameString应该是FileName. 另请注意，我在变量名称中放置了一些大写字母。这有助于您注意何时输入错误名称（使用小写字母），因为它无法解析为大写字母。

At any rate, I'd move the error-check inside the main loop:

无论如何，我会在主循环内移动错误检查：

Sub test()
Dim FolderPath As String
Dim FileName As String
Dim wb As Excel.Workbook

FolderPath = "C:\myDirectory\"
FileName = Dir(FolderPath & "*.xlsx")

Do While FileName <> ""
    On Error Resume Next
    Set wb = Workbooks.Open(FolderPath & FileName)
    If Err.Number <> 0 Then
        MsgBox ("Unable to open file " & FileName)
    End If
    On Error GoTo 0
    FileName = Dir
Loop

End Sub

EDIT: Per Sid's suggestion, added a "\" to the end of FolderPath.

编辑：根据 Sid 的建议，在FolderPath.

回答by Siddharth Rout

I am wondering why would Set wb = Workbooks.Open()fail at all if you are trying to open an xlsx file? unless the file is corrupted?

我想知道Set wb = Workbooks.Open()如果您尝试打开 xlsx 文件，为什么会失败？除非文件损坏？

The main reason why I see the code failing is because it is missing a "\"in the file path

我看到代码失败的主要原因是因为它"\"在文件路径中缺少一个

folderPath = "C:\myDirectory"
filename = Dir(folderPath & "*.xlsx")

Change the above to

把上面的改成

FolderPath = "C:\myDirectory"

If Right(FolderPath, 1) <> "\" Then FolderPath = FolderPath & "\"

FileName = Dir(FolderPath & "*.xlsx")

And if you missed the "\"by mistake in your question, then follow what Doug suggested (with a slight edit)

如果您"\"在问题中错误地错过了，请按照 Doug 的建议进行操作（稍作修改）

I added two lines to Doug's code

我在 Doug 的代码中添加了两行

If Right(FolderPath, 1) <> "\" Then FolderPath = FolderPath & "\"

and

和

Err.Clear

Here is the edited code.

这是编辑后的代码。

Sub test()
    Dim FolderPath As String, FileName As String
    Dim wb As Excel.Workbook

    FolderPath = "C:\myDirectory"

    If Right(FolderPath, 1) <> "\" Then FolderPath = FolderPath & "\"

    FileName = Dir(FolderPath & "*.xlsx")

    Do While FileName <> ""
        On Error Resume Next
        Set wb = Workbooks.Open(FolderPath & FileName)
        If Err.Number <> 0 Then
            MsgBox ("Unable to open file " & FileName)
            Err.Clear
        End If
        On Error GoTo 0
        FileName = Dir
    Loop
End Sub

回答by bto.rdz

the easiest way is to use on error resume next but that wont let you handle errors, it will just skip it.

最简单的方法是使用 on error resume next 但这不会让您处理错误，它只会跳过它。

to handle errors you can use on error go to . here are some examples of how to use both.

要处理错误，您可以在错误时使用。以下是如何使用两者的一些示例。

On Error

出错时

回答by Chris Rolliston

I'd either do what Doug Glancy suggests or define a helper function:

我要么按照 Doug Glancy 的建议做，要么定义一个辅助函数：

Function TryOpenWorkbook(ByVal FileName As String, ByRef WBook As Workbook) As Boolean
On Error Goto Except
  Set WBook = Workbooks.Open(FileName)
  TryOpenWorkbook = True
  Exit Function
Except:
  MsgBox Err.Description, vbWarning, "Error " & Err.Number
End Function

Sub Test()
  Dim folderPath As String
  Dim filename As String
  Dim wb As Workbook

  folderPath = 'C:\myDirectory'
  filename = Dir(folderPath & "*.xlsx")

  Do While filename <> ""
    If TryOpenWorkbook(folderPath & filename, wb) Then
      ' do stuff...
    End If
    filename = Dir
  Loop
End Sub