Skip Corrupt Workbooks In a loop

[litrainer]

I have a list of 34,000 *.xls files, some corrupt, some not, no way to tell.
I need a way to loop through them, ignoring "this file cannot be opened" error messages, open the valid ones, run my code, close the workbook and move on to the next.

I have a workbook listing all the filenames if this is a good place to start. Have no clue to loop through workbooks let alone skip over the corrupt ones

 

[greegan]

Maybe:

Option Explicit
 
Sub NewTest_20101101a()
Dim wb As Workbook
Dim CurrentFolder As String:    CurrentFolder = "special folder name"
Dim wbNames As Range:           Set wbNames = Range("GroupList[Group1]")
Dim Nm As Range
 
Application.DisplayAlerts = False
On Error Resume Next
 
For Each Nm In wbNames
    Set wb = Workbooks.Open("W:\directory\" _
         & CurrentFolder & "\" & Nm & ".xlsx")
 
    If Not wb Is Nothing Then
        Call OpenFile
        wb.Close False
    End If
Next Nm
 
End Sub

In another reply I stated with a few minor changes this works great, and thank you.
I've run into an issue on my end though. In one of the folders (where the macro looks for files named in the "group" list) it doesn't have one or more of the files named in the given list. What sort of error control should I use for this? thank you again,

-- g

 

[jbeaucaire]

The code should simply move on if it encounters a value in the group that doesn't result in a wb being opened.

We've turned on error resume and after we attempt to open a wb name, we test to see if it worked.

Hmm, maybe this tiny addition:

Option Explicit

Sub NewTest_20101101a()
Dim wb As Workbook
Dim CurrentFolder As String:    CurrentFolder = "special folder name"
Dim wbNames As Range:           Set wbNames = Range("GroupList[Group1]")
Dim Nm As Range

Application.DisplayAlerts = False
On Error Resume Next

For Each Nm In wbNames
    Set wb = Workbooks.Open("W:\directory\" _
         & CurrentFolder & "\" & Nm & ".xlsx")

    If Not wb Is Nothing Then
        Call OpenFile
        wb.Close False
        Set wb = Nothing
    End If
Next Nm

End Sub

 

[greegan]

This post sort of continues on with mine here
http://www.mrexcel.com/forum/showthread.php?t=507419
Where I'm asking for help to identify the specific column (Group 1 thru Group 5, or Group X). Would you be able to assist with that one as well?

-- g