Copy of File in filesearch result

[joseph micheli]

I'm using the filesearch in Access VBA to determine if a specified Excel file
exists within a specified subdirectory using the following:

With Application.FileSearch
.NewSearch
.LookIn = strSrceFolder
.FileName = strFile

When a copy of the Excel file has been created through Excel (opening the
file as read only and saving), the filesearch finds a match. The filename is
specified without any wildcard characters. Any work around suggestions would
be greatly appreciated.

Thanks.

 

[Klatuu]

I don't understand your question at all. What are you trying to accomplish?
If the file is in the folder and you can find the match, what is wrong?

 

[joseph micheli]

If the file is found, I want to perform a rename on the file to move it to
another subdirectory. The copy of the file makes my If Execute () true, but
when the rename command tries to rename the file using the name stored in
strFile, it fails because it cannot find the file. I do not want the copy of
the file to be mistaken for the actual file name.

 

[Klatuu]

If you just want to rename it, use the Name statement.
If you want to make a copy, use the FileCopy statement.
If you want to delete the original after copying, use the Kill statement.

Using the Filecopy followed by the Kill would be the same as using the Name
statement.

You can get details on these statements in VBA Help

 

[joseph micheli]

I don't have any trouble with the name statement. The problem is when the
file search finds the filename with the words "Copy of" in front of it. It
is causing the Execute statement to evaluate to true when it is, in fact
false.

 

[Dirk Goldgar]

I'm using the filesearch in Access VBA to determine if a specified
Excel file exists within a specified subdirectory using the following:

With Application.FileSearch
.NewSearch
.LookIn = strSrceFolder
.FileName = strFile

When a copy of the Excel file has been created through Excel (opening
the file as read only and saving), the filesearch finds a match. The
filename is specified without any wildcard characters. Any work
around suggestions would be greatly appreciated.

FileSearch is looking for an inexact match. I don't know if there's a
way to get it to return only an exact match. I recommend that you use
the Dir() function instead:

Dim strFilePath As String

strFilePath = strSrceFolder
If Right(strSrceFolder, 1) <> "\" Then
strFilePath = strFilePath & "\"
End If
strFilePath = strFilePath & strFile

If Len(Dir(strFilePath)) > 0 Then
' The file exists.
Else
' It doesn't.
End If

 

[Klatuu]

Dirk is correct. You had not mentioned the Copy Of before. In any case, the
Dir function is probably more widely used than the FileSearch.