Testing for filke in directory

[Guest]

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?

 

[Tom Ogilvy]

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

 

[Dave Peterson]

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

 

[Jon Peltier]

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/
_______

 

[Guest]

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?

 

[Tom Ogilvy]

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.