Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA--Suppress error message,corrupt file or unrecognizable format
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! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA--Suppress error message,corrupt file or unrecognizable format
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! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA--Suppress error message,corrupt file or unrecognizable format
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! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA--Suppress error message,corrupt file or unrecognizable for
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! |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA--Suppress error message,corrupt file or unrecognizable for
I've tried application.displayalerts = false, but then it does not capture
the error code for some reason and does not set FileIsCorrupt = True "Ryan H" wrote: 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! |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA--Suppress error message,corrupt file or unrecognizable format
in the OpenFileAndCheck
try adding Application.DisplayAlerts = False at the very beginning wasn't able to test it "laavista" wrote in message ... 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! |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA--Suppress error message,corrupt file or unrecognizable for
No, this doesn't work. It's strange, because if I put
application.displayalerts = false the error message is not displayed, but it opens the corrupt file and does not set an error code. If I don't use application.displayalerts = false, then if I select "cancel" on the message "file is not in recognizable format", then it sets an error code for which I can check. Any suggestions? "Patrick Molloy" wrote: in the OpenFileAndCheck try adding Application.DisplayAlerts = False at the very beginning wasn't able to test it "laavista" wrote in message ... 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! |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA--Suppress error message,corrupt file or unrecognizable for
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 -- Cheers, Ryan "laavista" wrote: 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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
file format or file extension is not valid...error message | Excel Discussion (Misc queries) | |||
How to suppress XML import error message | Excel Programming | |||
Unrecognizable format when opening file | Excel Discussion (Misc queries) | |||
Unrecognizable file format | Excel Discussion (Misc queries) | |||
Suppress error message with saveas | Excel Programming |