Prev: Resize Table Range to exclude zero values and Input New Range into a chart object
Next: Excel List
From: laavista on 18 Dec 2009 10:10
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!
From: Ryan H on 18 Dec 2009 10:20
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.
--
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!
From: Ryan H on 18 Dec 2009 10:42
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!
From: Patrick Molloy on 18 Dec 2009 11:25
in the OpenFileAndCheck
try adding
Application.DisplayAlerts = False
at the very beginning

wasn't able to test it

"laavista" <laavista(a)discussions.microsoft.com> wrote in message
news:32951CD0-0B20-4972-8DD3-8387B4A650B4(a)microsoft.com...
> 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!

 | 
Pages: 1
Prev: Resize Table Range to exclude zero values and Input New Range into a chart object
Next: Excel List