I am trying to determine the presence of a file using the
DIR command as follows:

If Len(Dir(path & wbook)0 Then
'File Found
End If

However, when the path variable is not available, my
macro aborts with the error message "Path not Found".

I'd appreciate any suggestions on how I can determine the
path is not present before I get the error message.

Thanks for any suggestions.

I just turn off the error checking and check to see if I found what I wanted.

Option Explicit
Sub testme()

Dim myPath As String
Dim wBook As String
Dim testStr As String

myPath = "\\myserver\mypath\excel\"
wBook = "book1.xls"
testStr = ""
On Error Resume Next
testStr = Dir(myPath & wBook)
On Error GoTo 0

If testStr = "" Then
MsgBox "filename not found--or folder/drive not found"
End If

End Sub



Michael wrote:

I am trying to determine the presence of a file using the
DIR command as follows:

If Len(Dir(path & wbook)0 Then
'File Found
End If

However, when the path variable is not available, my
macro aborts with the error message "Path not Found".

I'd appreciate any suggestions on how I can determine the
path is not present before I get the error message.

Thanks for any suggestions.

--

Dave Peterson

CheckPath: 'the next few lines of code will check to see if th
directory exists

With Application.FileSearch
.LookIn = ThisPath
If .LookIn < ThisPath Then
'if the directory does not exist, we will print
'an error message
MsgBox "Could not find the directory"
Else:
GoTo LookForFiles
End If
End With

LookForFiles:
'the next few lines of code will ensure that the file exists in tha
directory

With Application.FileSearch
.LookIn = ThisPath 'directory path
.Filename = "*.xls"
.SearchSubFolders = False

If .Execute() 0 Then
For i = 1 To .FoundFiles.Count
If FileYouWant = .FoundFiles(i) Then GoTo StartWork
Next i
Else
MsgBox "There were no files found in " & ThisFilePath
vbCritical
GoTo EndOfMacro
End If
NumberOfFilesFound = .FoundFiles.Count
End Wit

-----------------------------------------------
~~ Message posted from http://www.ExcelTip.com
~~View and post usenet messages directly from http://www.ExcelForum.com