add error handler for recursive loop in vba to skip over permission denied files/folders

Question

I'm trying to skip over files/folders that that throw "permission denied" but I don't really understand error handlers. I want it thus that if it there is an error thrown, it will skip to the next file or folder. My struggle here is understanding where to call it to resume in the code if I want to proceed to the next iteration or file/folder at hand. I'm also not sure if it should be 2 different error handlers for both file and folder loops.

If Not FSO.folderexists(DestinationFolder) Then FSO.Createfolder DestinationFolder
On Error GoTo NextFile

For Each oFile In FSO.Getfolder(Sourcefolder).Files

If FSO.getextensionname(oFile.Path) <> "pdf" Then

FSO.copyfile oFile.Path, DestinationFolder & " \ " & oFile.Name

Else


End If

Next oFile


For Each oFolder In FSO.Getfolder(Sourcefolder).SUbfolders

backupfiles oFolder.Path, DestinationFolder & " \ " & oFolder.Name

Next oFolder

NextFile: 

Resume NextFile2

NextFile2:

Next oFile

End Sub

You may want to also clear the error in the handler... https://stackoverflow.com/questions/1038006/good-patterns-for-vba-error-handling — braX, May 03 '21 at 01:44

Toddleson · Answer 1 · 2021-05-05T13:12:03.933

0

Since you want to resume yor program after encountering errors, an error handler might not be what you're looking for. Use On Error Resume Next and then test Err.Number <> 0 to see if the current file has given an error. If yes, then Goto NextFile and reset the error count with Err.Clear. Here is an example loop:

Sub test()
    Dim b As Integer, i As Integer
    
    On Error Resume Next
    For i = 1 To 10
        'Test statement
        b = (-i) ^ (-1 / 2) 'causes an error
        
        'Error Checker
        If Err.Number <> 0 Then
            Err.Clear
            GoTo nexti
        End If
        
        'Rest of your code
        MsgBox "Stuff"
        
nexti:
    Next i
    On Error GoTo 0
End Sub

Here is what that looks like with a double loop:

Sub test()
    Dim b As Integer, i As Integer, Errcount As Integer
    
    On Error Resume Next
    For j = 1 To 10
        For i = 1 To 10
            'Test statement
            b = (-1) ^ (-1 / 2) 'causes an error
            
            'Error Checker
            If Err.Number <> 0 Then
                Errcount = Errcount + 1
                Err.Clear
                If Errcount > 4 Then GoTo nextj Else GoTo nexti
            End If
            
            'Rest of your code
            MsgBox "Stuff"
            
nexti:
        Next i
        
        'Code for a successful first loop
        MsgBox "Good Loop"
nextj:
        Errcount = 0
    Next j
    
    On Error GoTo 0
    
End Sub

Explanation: As before, this is avoiding the use of error handlers so that code can resume after skipping items that cause errors. Here there are two Goto tags that can be used to skip to the next iteration of the inner or outer loop. In your code these would be Next File and Next Folder. When an error is detected, we can decide which tag we want to skip to, deciding between skipping the current file or the entire folder.

edited May 05 '21 at 13:12

answered May 03 '21 at 13:44

Toddleson

4,321
1
6
26

wouldn't you also need a ```next i``` in your code? in my loop I have ```for ofile``` and then ```next ofile``` so when I add ```next ofile``` in the error handler it throws the error "for without next". How do you get around that? – May 04 '21 at 21:31
`Next i` is there. I purposefully avoid using error handlers because this way the code can continue while skipping items that cause errors. Instead I test for the presence of an error, and if found, I skip to the end of the loop. `Goto` is not the same as `On Error Goto`. My example is just a regular For Loop, no error handlers included. – Toddleson May 04 '21 at 22:05
oh I thought the error handler was ```on error goto ```. But I think i see here because you're not using ```exit sub``` so the code just pases through ```next i```anyways. – May 05 '21 at 01:20
so in my case nexti would continue to the next file but what if you got an error again on the proceeding file and wanted to exit that folder to the more parent folder? – May 05 '21 at 01:22
I'll add an example that uses a double loop. – Toddleson May 05 '21 at 12:57
Why did you set ```Errcount > 4```, does this mean if there 4 errors then it will proceed to the next i or whatever? – May 06 '21 at 04:24
I just set it to an arbitrary number as an example of what is possible. In my example, when Errcount reaches 5, it skips to nextJ – Toddleson May 06 '21 at 13:14
Ok. how come you don't need another end if after one of the ifs in the error statement – May 07 '21 at 05:38
If you write the statement as a single line, you can skip the End If. Its just a syntax thing. – Toddleson May 07 '21 at 13:19

score 0 · Answer 2 · answered May 04 '21 at 14:38

Try this:

For Each oFile In FSO.Getfolder(Sourcefolder).Files

 On Error GoTo 100:

    If FSO.getextensionname(oFile.Path) <> "pdf" Then
    
        FSO.copyfile oFile.Path, DestinationFolder & " \ " & oFile.Name
    
    Else

    End If 

100:
Next ofile

add error handler for recursive loop in vba to skip over permission denied files/folders

2 Answers2