This works for the unrecognizable format, but not for the corrupt file (and
my user has one).

Thanks for the idea, though!




"Ryan H" wrote:

There might be another option for you. You could get the file extension of
the file your loop is currently testing and compare that extension with
extensions you want by calling a function.

' intergrate this into your existing loop with FileName is assigned

Dim strFileExtension As String

FileName = "filename.qmf"

strFileExtension = Mid(FileName, InStr(FileName, "."))

If IsFileAllowed(strFileExtension) = False Then
MsgBox "This file can't be recognized by Excel."

' or do something else
End If

End Sub


Function IsFileAllowed(ext As String) As Boolean

Dim myArray As Variant

' fill array with extension you want
myArray = Array(".xls", ".xlms", ".xla")

On Error GoTo ErrorHandler
If WorksheetFunction.Match(ext, myArray, 0) 0 Then
IsFileAllowed = True
End If

ErrorHandler:

End Function
--
Cheers,
Ryan


"laavista" wrote:

I'm using Excel 2003.

I'm looping through files, opening them, and writing info into another Excel
spreadsheet. I need to check for corrupt excel files and unrecognizable
formats. I'm getting close, but I'm getting the error message "this file is
not in a recognizable format" and the user would have to remember to select
"cancel" (and not "OK"). I'd like to suppress this error message.

My code:

In my sub:

Global FileIsCorrupt as boolean
Global NewFileToCheck as string
Global FileName as string
Global Path as string

(Filename and path are set at this point...)

OpenFileAndCheck 'call function

If FileIsCorrupt = True then 'if true, file is corrupt or
unrecognizable
msgbox ("msg to user that it is corrupt and file is being skipped')
GoTo FoundCorruptFile ' skips over writing info from file
End if

=====
Function OpenFileAndCheck() as Boolean

On Error GoTo ErrHandler
FileIsCorrupt = False 'set to false--it will be reset to true if
file is corrupt

Set NewFileToCheck = Workbooks.Open(Filename:=Path & FileName)

Exit Function

ErrHandler:
If Err < 0 then
FileIsCorrupt = Tre
End if

End Function
=====

Your help would be so appreciated!