Jon just a few questions about your suggestion to use the function.

1). Is filespec as used in this line the path to the file? Attr =
GetAttr(FileSpec)

2). What is vbDirectory, I think it is a VBA constant isn't it?

3). To use the Function, I simply callit? Do I need to worry about passing
it a variable such as filename or directoy path?


"Jon Peltier" wrote:

The problem with using Dir is that, if the program is cycling through a directory
with Dir, the specific call to check for one file will reset the Dir parameters, and
the rest of the program's first Dir cycle will be compromised.

I have borrowed this function from VB MVP Karl Peterson, which avoids the problem
stated above:

Private Function FileExists(ByVal FileSpec As String) As Boolean
' Karl Peterson MS VB MVP
Dim Attr As Long
' Guard against bad FileSpec by ignoring errors
' retrieving its attributes.
On Error Resume Next
Attr = GetAttr(FileSpec)
If Err.Number = 0 Then
' No error, so something was found.
' If Directory attribute set, then not a file.
FileExists = Not ((Attr And vbDirectory) = vbDirectory)
Else
m_ErrorText = Err.Description
End If
End Function

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

Helen wrote:
I have read through some of the posts but cannot find an answer to the
question of - - How do you test for the existance of a file in a directory.

I can create and navigate to directories based upon month of the year as per
my example code:
sPath = "E:\LE\LE " & XlMonth2 & " 05\" & "29382_" & XlMonth2 &
"_CIS_Ph1&2 Roll Up .xls"

xlApp.Workbooks.Open sPath ' & Format(Date, "yyyymmdd.xls")
xlApp.Workbooks.Open (sPath), 0, ReadOnly = True
xlApp.ActiveWorkbook.Windows(1).Visible = False

But how do I test if the file is there before trying to open it in case the
file has not yet been deposited by the accounting departmnet yet?