How to skip iteration immediately in VBA for loop if any error occurs-CodePudding

I have for loop in VBA that goes through xlsx. files placed in one folder. The script is about to open each file from 1000 input files, extract information and save in extract files. The thing is usually 2-3 files out of 1000 are corrupted or return an access error. How can I properly use On Error GoTO or another statement to simply skip iteration if any error occurs within the whole for loop and go (right after an error occurs, without ending the current iteration) to the next iteration (so next file)? I tried multiple setups with On Error, GoTo 0, and GoTo Next but every time I get either errors or my extracted files have duplicated names from the previous error file and include no data, just blank template. Here is a representation of my code without any error-handling statements. Would be much appreciated for help:

Sub Separator()

      Dim i as Variant
      [declaring variables and setting paths]

      set oFolder = oFSO.GetFolder(Path_String)

      For Each oFile In oFolder

        sFileOutput = Path_String & "\" & "Extract_File_Template.xlsm"
        sFileInput = Path_String & "\" & i & ".xlsx"

        set wb_output = Workbooks.Open(sFileOutput) 
        set wb_input = Workbooks.Open(oFileInput)

        [data wrangling, saving files]

        Next oFile

End Sub

CodePudding user response：

The standard way of addressing this issue is to put the function which may fail inside a try function'


Public Function TryOpenDoc(byval ipPath as string, byref opDoc as document) as Boolean
    On Error Resume Next
    Set opDoc = Workbooks.Open(ipPath) 

    TryOpenDoc = (err.number = 0)
    on error goto 0
exit function

' so now you can write

If TryOpenDoc(sFileoutPut, wb_ouput) then
    'Do the happy path stuff
else
    ' do the stuff for a file failing to open
end if

CodePudding user response：

I found that the easiest way to handle this issue isn't through skipping iteration but simply adding CorruptLoad argument to the Open function. This opens the file despite the fact it's corrupted and moves on. Thanks all for help!

Workbooks.Open(sFileInput, CorruptLoad:=XlCorruptLoad.xlRepairFile)