VBA--Suppress error message,corrupt file or unrecognizable format

[laavista]

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!

 

[Ryan H]

Have you tried using

Application.DisplayAlerts = True

'code where error occurs

Application.DisplayAlerts = False

Hope this helps! If so, let me know, click "YES" below.

 

[Ryan H]

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

 

[laavista]

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

Thanks for the idea, though!

 

[laavista]

I've tried application.displayalerts = false, but then it does not capture
the error code for some reason and does not set FileIsCorrupt = True

 

[Patrick Molloy]

in the OpenFileAndCheck
try adding
Application.DisplayAlerts = False
at the very beginning

wasn't able to test it

 

[Ryan H]

Ok, so we solved the file extension issue. And now you are wondering about
the corrupt file stuff. Try this sequence of code. Hope this helps! If so,
let me know, click "YES" below.

Option Explicit

Dim NewFileToCheck As Workbook

Sub YourMainSub()

Dim strFileExtension As String

Filename = "filename.qmf"

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

' check file extensions first
If IsFileAllowed(strFileExtension) = False Then
MsgBox Filename & " can't be recognized by Excel.", vbExclamation
End If

' check if file is corrupt
If IsFileCorrupt Then
MsgBox NewFileToCheck.Name & " is corrupt and file is being
skipped.", vbCritical
GoTo FoundCorruptFile ' skips over writing info from file
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


Function IsFileCorrupt() As Boolean

On Error GoTo ErrorHandler
Set NewFileToCheck = Workbooks.Open(Filename:=Path & Filename)

Exit Function

ErrorHandler:
If Err.Number <> 0 Then IsFileCorrupt = True

End Function