CODEWITHSUNDEEP
vbaforeachif-statement
Kristof I.
Kristof I.

Reputation: 25

How to have for each loop in vba skip to next iteration?

I got a file reader from excelexperts.com that lists the files within a folder (and has an option to include subfolders). I decided to tweak the code to skip "Thumbs.db" files.

I've actually got the code working with a GoTo statement as a last resort but I know this is considered bad programming and was wondering what the proper code would be.

Dim iRow

Sub ListFiles()
    iRow = 11
    Call ListMyFiles(Range("C7"), Range("C8"))
End Sub

Sub ListMyFiles(mySourcePath, IncludeSubfolders)
    Set MyObject = New Scripting.FileSystemObject
    Set mySource = MyObject.GetFolder(mySourcePath)
    On Error Resume Next
    For Each myFile In mySource.Files
        **If myFile.Name = "Thumbs.db" Then
            GoTo nextone
        Else**
            iCol = 2
            Cells(iRow, iCol).Value = myFile.Path
            iCol = iCol + 1
            Cells(iRow, iCol).Value = myFile.Name
            iCol = iCol + 1
            Cells(iRow, iCol).Value = myFile.Size
            iCol = iCol + 1
            Cells(iRow, iCol).Value = myFile.DateLastModified
            iRow = iRow + 1
        **End If**
**nextone:**
    Next
    If IncludeSubfolders Then
        For Each mySubFolder In mySource.SubFolders
            Call ListMyFiles(mySubFolder.Path, True)
        Next
    End If
End Sub

The parts I added in are encapsulated in double stars.

In my previous attempt, I had added in the following code:

If myFile.Name = "Thumbs.db" Then
    Next
Else
    rest of the code here
End If

But I got the "Next without For" error. That's why I went with the GoTo statement that I'm hoping to replace with something better.

Upvotes: 1

Views: 10068

Answers (1)

Mike J
Mike J

Reputation: 1154

Just change the logic of your If statment around to:

For Each myFile In mySource.Files
    If myFile.Name <> "Thumbs.db" Then
        iCol = 2
        Cells(iRow, iCol).Value = myFile.Path
        iCol = iCol + 1
        Cells(iRow, iCol).Value = myFile.Name
        iCol = iCol + 1
        Cells(iRow, iCol).Value = myFile.Size
        iCol = iCol + 1
        Cells(iRow, iCol).Value = myFile.DateLastModified
        iRow = iRow + 1
    End If
Next

If you are going to have additional logic on how you skip files in the future, you should move the detection logic to a separate method.

For Each myFile In mySource.Files
    If ShouldProcessFile(myFile) Then
        iCol = 2
        Cells(iRow, iCol).Value = myFile.Path
        iCol = iCol + 1
        Cells(iRow, iCol).Value = myFile.Name
        iCol = iCol + 1
        Cells(iRow, iCol).Value = myFile.Size
        iCol = iCol + 1
        Cells(iRow, iCol).Value = myFile.DateLastModified
        iRow = iRow + 1
    End If
Next

Function ShouldProcessFile(file)
    ShouldProcessFile = True
    If file.Name = "Thumbs.db" Then ShouldProcessFile = False
End Function

Upvotes: 1

Related Questions