Error handling only works once?

[MrPez]

I'm trying to do some code on a folder system with hundreds of excel files. The code I have is only applicable to some of the files and I want those to be amended and saved. The rest are to be ignored.

I have adapted the code from Excel VBA: Loop Through a Folder of Excel Workbooks. FileSearch Method and have attempted to add in some error handling to skip over the files that are not applicable.

For some reason the error handling works for the first file but then the second time I just get a run-time error again. Any ideas?

Here is my code

Sub RunCodeOnAllXLSFiles()Dim lCount As Long
Dim wbResults As Workbook
Dim wbCodeBook As Workbook
Dim FailPath As String
Dim CurrentSheet As Worksheet
Dim FileToOpen As Variant




Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.EnableEvents = False




    Set wbCodeBook = ThisWorkbook
        With Application.FileSearch
            .NewSearch
            .LookIn = "C:\test_folder"
            .SearchSubFolders = True
            .FileType = msoFileTypeExcelWorkbooks
                If .Execute > 0 Then 'Workbooks in folder
                    For lCount = 1 To .FoundFiles.Count 'Loop through all
                        'Open Workbook x and Set a Workbook variable to it
                        Set wbResults = Workbooks.Open(Filename:=.FoundFiles(lCount), UpdateLinks:=0)
                        
                                'lots of code goes here (dealing with graphs)




                        wbResults.Close SaveChanges:=True


Failed:
                        'record the failed units in log file
                        If Err.Number <> 0 Then
                            Open "C:\FailLog.txt" For Append As #1    'if file does not exist, it will be created
                            Print #1, Application.ActiveWorkbook.FullName
                            Close #1
                            wbResults.Close SaveChanges:=False
                        On Error GoTo 0
                        Resume
                        End If
                        
                    Next lCount
                End If
        End With
On Error GoTo 0
Application.ScreenUpdating = True
Application.DisplayAlerts = True
Application.EnableEvents = True
End Sub

 

[Gary McMaster]

Just a guess. Maybe you need to clear the error object after you handle the error. Help states that "Resume" clears the error if it was invoked by "On Error Resume Next" but it's a little vague on whether or not the same thing happens for "On Error Goto"

Err.Clear

Gary

 

[Cool blue]

Maybe it's just my eyes but I can't see an On Error GoTo Failed statement. I didn't fully parse the code but you will either need that or an On Error Resume Next.

 

[MrPez]

Gary - thanks but I tried changing to err.clear and it had no effect.
Cool Blue - well spotted! I cut the 'On Error GoTo Failed:' out with the rest of the code by mistake:

   Set wbCodeBook = ThisWorkbook        With Application.FileSearch
            .NewSearch
            .LookIn = "S:\iprs\diagrad\Surveys\TandG\Unfors Templates\JD\TG\2010_Compatable\test_folder"
            .SearchSubFolders = True
            .FileType = msoFileTypeExcelWorkbooks
                If .Execute > 0 Then 'Workbooks in folder
                    For lCount = 1 To .FoundFiles.Count 'Loop through all
                        'Open Workbook x and Set a Workbook variable to it
                        Set wbResults = Workbooks.Open(Filename:=.FoundFiles(lCount), UpdateLinks:=0)
                        
                               'Code goes here
                                    On Error GoTo Failed:
                                    




                        wbResults.Close SaveChanges:=True


Failed:
                        'record the failed units in log file
                        If Err.Number <> 0 Then
                            Open "S:\iprs\diagrad\Surveys\TandG\Unfors Templates\JD\TG\2010_Compatable\FailLog.txt" For Append As #1    'if file does not exist, it will be created
                            Print #1, Application.ActiveWorkbook.FullName
                            Close #1
                            wbResults.Close SaveChanges:=False
                        Err.Clear
                        End If
                        
                    Next lCount
                End If
        End With
On Error GoTo 0
Application.ScreenUpdating = True
Application.DisplayAlerts = True
Application.EnableEvents = True
End Sub

 

[Gary McMaster]

I didn't mean to replace the "Resume" with "Err.Clear". I believe you would need both (Resume is usually the last line in the error handler)

Also, It looks like you have that error handler in the middle of a loop. It is going to execute the error code every time through the loop (if errornum <> 0). It may never be zero if you don't clear it after an error is raised. Usually an "On Goto" error handler is not in the middle of the code as you have it. It is usually placed just before the "End Sub" and an "Exit Sub" is placed just before the error handler so that the error code does not execute unless an error is raised. The label "Failed:" causes it to jump over the "Exit Sub".

"On Error Resume Next" can be used inline as you seem to be attempting to do.

Gary

 

[Norie]

What error are you trying to 'handle'?