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?

sPath = "E:\LE\LE " & XlMonth2 & " 05\" & "29382_" _
& XlMonth2 & "_CIS_Ph1&2 Roll Up .xls"
if dir(spath) < "" Then
xlApp.Workbooks.Open sPath, 0, ReadOnly = True
xlApp.ActiveWorkbook.Windows(1).Visible = False
else
msgbox sPath & vbNewLine & _
"was not found"
end if

--
Regards,
Tom Ogilvy


"Helen" wrote in message
...
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?

You could use something like:



dim testStr as string
dim sPath as string

sPath = "..............."

teststr = ""
on error resume next
teststr = dir(sPath)
on error goto 0

if teststr = "" then
msgbox "not found"
else
Msgbox "Found it"
end if



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?

--

Dave Peterson

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?

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?

Why not use Dir if you are not looping through a directory using Dir (which
was Jon's caution - If you were, then you would already know the file
exists and not need the function anyway unless this was some very
specialized situation which I doubt).

filespec is the same argument you would provide Dir. In your case it would
be sPath.

--
Regards,
Tom Ogilvy, Microsoft Excel MVP

"Helen" wrote in message
...
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?