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!

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" wrote:

> 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!
>

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.

"Mike H" wrote:

> 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" wrote:
>
> > 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!
> >

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

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

"Paul" wrote:

> This is a bit of a long answer, but it works well for us in a similar scenario.
>
> Sub Test_File_Access()
>
> cFile = ' set to the name of the file you need to activate
> cDirect = ' set to the folder containing the files
>
> lClose = False
> lOpen = ZZZZ_SelectFile(cFile)
> If lOpen = False Then
> lClose = True
> lOpen = ZZZZ_OpenFile(cFile, cDirect, True, False, False)
> If lOpen = False Then
> n = MsgBox("Expected file not found", vbInformation)
> Exit Sub
> End If
> End If
> ' Do your stuff here
>
> ' The next bit closes the data file again (otherwise you end up with masses
> of files opened)
> If lClose = True then
> Application.CutCopyMode = False
> Windows(cFile).Activate
> ActiveWorkbook.Close (False)
> End If
>
> End Sub
>
> Function ZZZZ_OpenFile(pFile, pDirect, pReadOnly, pUpdateLinks, pMessage)
> ' Attempts to open a specified file
> ' Returns True if the operation was successful
> ' Returns False if the operation failed
> ' pFile is the file to be opened
> ' pDirect is the directory in which the file is to be found
> ' pReadOnly determines whether the file is to be opened read only
> ' pUpdateLinks determines whether any file links are updated when opening
> ' pMessage detgermines whether s fail message is displayed to the user
>
> ' Set ZZZZ_OpenFile to true - it will be reset to false if the operation fails
> ZZZZ_OpenFile = True
> ' cOpenFile is the full path and filename to be opened
> pOpenFile = Trim(pDirect) + Trim(pFile)
> ' Set error trap to capture a failure to open
> On Error GoTo NotOpen
> ' Atempt to open the specified file
> Workbooks.Open Filename:=pOpenFile, ReadOnly:=pReadOnly,
> UpdateLinks:=pUpdateLinks
> ' Reset the error trap to Excel defaults
> On Error GoTo 0
> ' If the operation failed and messages are to be displayed
> If ZZZZ_OpenFile = False And pMessage = True Then
> ' Display the message to the user
> nResponse = MsgBox(pOpenFile + " doesn't exist", vbCritical)
> End If
> Exit Function
> NotOpen:
> ' Reset ZZZZ_OpenFile to false when the operation fails
> ZZZZ_OpenFile = False
> Resume Next
> End Function
>
> Function ZZZZ_SelectFile(pFile)
> ' Attempts to select a specified file
> ' Returns True if the operation was successful
> ' Returns False if the operation failed
> ' pFile is the file to be selected
> ' Set ZZZZ_SelectFile to true - it will be reset to false if the operation
> fails
> ZZZZ_SelectFile = True
> ' Set error trap to capture a failure to select
> On Error GoTo NotOpen
> ' Select the specified file
> Windows(pFile).Activate
> ' Reset the error trap to Excel defaults
> On Error GoTo 0
> Exit Function
> NotOpen:
> ' Reset ZZZZ_OpenFile to false when the operation fails
> ZZZZ_SelectFile = False
> Resume Next
> End Function
>
>
>
>
>
>
> "laavista" wrote:
>
> > 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!
> >