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!

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!

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!