How check if file to open is Excel or if Excel file is corrupt?

[laavista]

I'm using Excel 2003. I'm looping through a set of Excel files, opening
each one and writing data from that Excel spreadsheet into a "master" excel
spreadsheet.

The program failed when one of the Excel files was corrupt.
Also--occasionally the user will have a file in a different format (e.g.,
Word) in the directory I'm working with, and that's causing problems.

1) How do I check that the file to be opened IS .xls?
2) How do I check that the Excel file is not corrupt?

Your help would be GREATLY appreciated.

THANKS!

 

[Mike H]

Hi,

I would do this with 2 subs, the first to open each .xls file in turn and
the second to do whatever it is you want. Change the path in the first sub to
your path. The second sub in my samp;e code simply displays the filename.

Sub LoopThroughDirectory()
Application.DisplayAlerts = False
'Change this to your directory
MyPath = "C:\"
ActiveFile = Dir(MyPath & "*.xls")
Do While ActiveFile <> ""
Workbooks.Open Filename:=MyPath & ActiveFile
'Here is the line that calls the macro below, passing the workbook to it
DoSomething ActiveWorkbook
ActiveWorkbook.Save
ActiveWorkbook.Close
ActiveFile = Dir()
Loop

Application.DisplayAlerts = True
End Sub

Sub DoSomething(Book As Workbook)
MsgBox ActiveWorkbook.Name
End Sub

Mike

 

[laavista]

Thanks. I'm able to open and loop through the spreadsheets, but it fails
when the excel file is corrupt or if it tries to open a non-Excel file.

 

[laavista]

Thank you so much. This is great and is what I needed.

I really appreciate you taking the time to post the answer!