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

 

[jbeaucaire]

In your macro:

1) Declare a wb variable
2) Cycle through your list of filenames one at a time feeding them into the wb variable
3) Attempt to open the wb with an "On Error Resume Next" prior instruction
4) After the open command, look to see if the wb variable now resolves to the desired workbook, meaning it opened successfully. If so, proceed with other commands, if not, skip to next loop.

Dim wb As Workbook
Dim wbNames as Range
Dim Nm as Range
Application.DisplayAlerts = False

Set WbNames = Range("A1:A34000")

On Error Resume Next
For Each Nm in WbNames
    Set wb = Workbooks.Open("C:\Files\" & Nm)
    If Not wb Is Nothing Then

        'your commands here

    End if
Next Nm

 

[litrainer]

Re: Skip Corrupt Workbooks In a loop- thanks isn't enough

I included my complete code- besides adding my own code (change the file name based on cell content) I added a whole bunch of on-error-resume lines.
I never paid much attention to error codes because I always created and ran my own macros; if there was an error during test I'd fix it.

This was different, and as I ran the list it would stop with errors after the file opened. I kept putting in the error statments until the errors stopped.

All but one, the I/O device error. Never could surpress that one. So I got a book and a drink and sat there as the macro ran. Every time it beeped, I clicked "No" and it went on its merry way. Yes it took a couple of hours but this is a one time project and it sure beat opening them one at a time.

Thanks again,

michael

Code for the Curious
___________________

Option Explicit
Sub file_opener()
Dim wb As Workbook
Dim wbNames As Range
Dim myfile1 As Range
Dim newname As Variant
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Set wbNames = Range("A1:a34195") 'range of excel filenames
On Error Resume Next
For Each myfile1 In wbNames
Set wb = Workbooks.Open("z:\" & myfile1) 'where the files are
On Error Resume Next
If Not wb Is Nothing Then 'do this if there is an open file
On Error Resume Next
Cells.UnMerge 'won't work if the cell isn't there
'create the new name
newname = Range("a2").Text & Range("c4").Text & Range("h7").Text & Int(Rnd * 100000) 'rnd names the file if all cells are empty
On Error Resume Next
'save the file
wb.SaveAs Filename:="\\z:\it document\mg1\" & " " & newname & " " & ".xls"
On Error Resume Next
'close the file
wb.Close
On Error Resume Next
End If
On Error Resume Next
Next myfile1 'do it again
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub

 

[greegan]

In your macro:

1) Declare a wb variable
2) Cycle through your list of filenames one at a time feeding them into the wb variable
3) Attempt to open the wb with an "On Error Resume Next" prior instruction
4) After the open command, look to see if the wb variable now resolves to the desired workbook, meaning it opened successfully. If so, proceed with other commands, if not, skip to next loop.

Dim wb As Workbook
Dim wbNames as Range
Dim Nm as Range
Application.DisplayAlerts = False
 
Set WbNames = Range("A1:A34000")
 
On Error Resume Next
For Each Nm in WbNames
    Set wb = Workbooks.Open("C:\Files\" & Nm)
    If Not wb Is Nothing Then
 
        'your commands here
 
    End if
Next Nm

Above from Skip Corrupt Workbooks In a loop


This actually (sort of) answered my question in this thread...
http://www.mrexcel.com/forum/showthread.php?t=502735

I was able to specify the table column and all of the files opened... but as far as running the macro it didn't work as well as i hoped.

I wanted one file to open and run the macro and go to the next file. if the cell was empty or the file didn't exist, I would like it to skip to the next file name or end.

Also the directory changes each month. I'm hoping I can fix this on my own... The last folder changes depending on the current accounting period.

Anyways I didn't know if I should post in my thread or this one, but if are able to shed some light on this that would be great!

-- g

 

[jbeaucaire]

As far as I can see, the code I suggested should skip files it failed to open or that don't exist. I don't actually have any corrupt files, but I guess with DisplayAlerts off it will just open the corrupted files as gibberish.

If all your files have a specific format, perhaps the gibberish can help you. For instance if all your files have a specific value in the same cell, you can test for that.

This code also puts the filepath at the top in a variable making it easy to edit.

Dim wb As Workbook
Dim wbNames As Range
Dim Nm As Range
Dim fPath As String:    fPath = "C:\2010\"
Application.DisplayAlerts = False
 
Set wbNames = Range("A1:A34000").SpecialCells(xlConstants)
 
For Each Nm In wbNames
    Set wb = Workbooks.Open(fPath & Nm)
    If Not wb Is Nothing Then
        
        If Range("A1") = "Company Name" Then
            'your commands here
        End If
        
        wb.Close False
    End If
Next Nm

 

[greegan]

Its more like... that it works to open the files
but it doesn't open one file, run my macro then opens the next (which is what I want)
it's more like open all of the files, skipping my macro

-- g

 

[jbeaucaire]

Well, my code also closes the files, so all the files being open is odd.

How are you calling the extra code? Are you putting the code in where marked? You do know you aren't showing me anything substantive to really help "see' what you're seeing?

 

[greegan]

Sub NewTest_20101101a()
' == Works to a degree _
    Will open all files in table column but will not process macro
Dim wb As Workbook
Dim CurrentFolder
    CurrentFolder = "special folder name"
Dim wbNames As Range
Dim Nm As Range
'Application.DisplayAlerts = False
Set wbNames = Range("GroupList[Group1]")
On Error Resume Next
For Each Nm In wbNames
    Set wb = Workbooks.Open("W:\directory\" _
    & CurrentFolder & "\" & Nm & ".xlsx")

    If Not wb Is Nothing Then
    End If
    Call OpenFile 
' my macro copies data  from the open file in 4 or 5 steps once its _
its  finished copying, i want it  to close the file and go to the next _
file name, open it and then copy data again, until the range is finished.

Next Nm
End Sub

I hope this helps to explain it.

 

[jbeaucaire]

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

 

[greegan]

I don't exactly understand how it is working but it does now with a few tweaks. Thank you for your help on this.

-- g