programmaticaly test more then 1000 excel files for "opening Errors"
 

Ref. Windows 2000 proff.
Excel 97, Excel 2K

I would like to programmaticaly test more than 1000
excel files for "opening Errors" like ...
1)This file is not in a recognizable format ....
or
2)'File Name.xls' cannot be accessed ....
or Whatever opening error

I think the macro shoul work like this.

for each Excel file in c:/documents
open the file, ( and without the user prompt ... )
if there is an excel error message ...
log file name in bad c:\files.txt
else
log file name in good c:\files.txt
end if
next file

Any Help ? Thanks.

Best Regards.

Robert.

programmaticaly test more then 1000 excel files for "opening Errors"
 

some thing like below /

tim

Sub CheckAll()

'adjust your path to suit
Const sPath As String = "C:\Analysis\test\"

Dim d As Worksheet
Dim wb As Workbook, i As Integer

Set d = ThisWorkbook.Sheets("Progress log")

With d
.Cells.Clear
'Set up Column Headers
.Cells(1, 1) = "Path"
.Cells(1, 2) = "State"
End With

With Application.FileSearch
.NewSearch
.LookIn = sPath
.SearchSubFolders = True
.Filename = "*.xls"

If .Execute() Then
For i = 1 To .FoundFiles.Count

d.Cells(i + 1, 1).Value = .FoundFiles(i)

Set wb = Nothing
On Error Resume Next
Set wb = Workbooks.Open(.FoundFiles(i))
On Error GoTo 0

If wb Is Nothing Then
d.Cells(i + 1, 1).Value = Err.Description
Err.Clear
Else
d.Cells(i + 1, 2).Value = "OK"
wb.Close False
End If
Next i
End If
End With

End Sub




"Robert" wrote in message
...
Ref. Windows 2000 proff.
Excel 97, Excel 2K

I would like to programmaticaly test more than 1000
excel files for "opening Errors" like ...
1)This file is not in a recognizable format ....
or
2)'File Name.xls' cannot be accessed ....
or Whatever opening error

I think the macro shoul work like this.

for each Excel file in c:/documents
open the file, ( and without the user prompt ... )
if there is an excel error message ...
log file name in bad c:\files.txt
else
log file name in good c:\files.txt
end if
next file

Any Help ? Thanks.

Best Regards.

Robert.

programmaticaly test more then 1000 excel files for "opening Errors"
 

It works.
Thank You.
Regards, Robert.