Check or a file exist give wrong result

[Gerritvans]

Hi,

I have a number of txt files in a directory and wants to read them b
EXCEL.
The names of the files are defined by a formula.
However sometimes the formula create a filename which not reall
exists. So before I open the file in EXCEL I want to check or that fil
exists. To do that I used the code below:

Sub test()
With Application.FileSearch
.NewSearch
.LookIn = (Interaction.Environ$("userprofile") & "\M
Documents\Test files\")
.SearchSubFolders = False
.Filename = "up12_7.txt"
.MatchTextExactly = True
.FileType = msoFileTypeAllFiles
If .Execute() > 0 Then
MsgBox "There were " & .FoundFiles.Count & _
" file(s) found."
For i = 1 To .FoundFiles.Count
MsgBox .FoundFiles(i)
Next i
Else
MsgBox "There were no files found."
End If
End With

End Sub

The problem is that in this case the MsgBox will report that 2 file
exist, "up12_7.txt" and "up12_70.txt", inspite the fact that
.MatchTextExactly = True is used.
How can I change this, so I'm sure it will only look to the fil
"up12_7.txt"?
Thanks for you help by forehand.

Best regards,

Gerri

 

[tinyjack]

I would not use the FileSearch object to do this, you can just use th
Dir function:


Code
-------------------

Sub test()

Dim strFound As String

strFound = Dir(Interaction.Environ$("userprofile") & "\My Documents\Test files\up12_7.txt")

If Len(strFound) > 0 Then
MsgBox "File found"
Else
MsgBox "File NOT found."
End If

End Sub

-------------------


HTH

T

 

[Tom Ogilvy]

MatchTextExactly has nothing to do with the file name. It is a parameter
for checking words/strings contained in a file.

The simple solution is to loop through he returned files and test for an
exact match with your filename string.

 

[Gerritvans]

I would not use the FileSearch object to do this, you can just us
the Dir function:
Code
-------------------

Sub test()

Dim strFound As String

strFound = Dir(Interaction.Environ$("userprofile") & "\My Documents\Test files\up12_7.txt")

If Len(strFound) > 0 Then
MsgBox "File found"
Else
MsgBox "File NOT found."
End If

End Sub
-------------------


HTH

TJ

Thanks TJ, your solution is working fine. But I still not understan
what was wrong with my solution...

Gerri

 

[Tom Ogilvy]

Even after I explained it to you? What part don't you understand?

--
Regards,
Tom Ogilvy

I would not use the FileSearch object to do this, you can just use
the Dir function:
Code:
--------------------

Sub test()

Dim strFound As String

strFound = Dir(Interaction.Environ$("userprofile") & "\My Documents\Test files\up12_7.txt")

If Len(strFound) > 0 Then
MsgBox "File found"
Else
MsgBox "File NOT found."
End If

End Sub
--------------------


HTH

TJ

Thanks TJ, your solution is working fine. But I still not understand
what was wrong with my solution...

Gerrit

 

[Gerrit van Stempvoort]

Hi Tom,

I used the solution send in by tinyjack before you send in your solution.
When I psot my reply I did not notice that you had send another solution to.
From you I understand that MatchTextExactly = True has nothing to do with
filename, but still it is strange to me that if I ask for Filename =
"up12_7.txt" the filename "up12_70.txt" is also included. I'm afraid that
I'm not the only one that thinks like that and get an error because of this.

Anyway thanks for your solution and support. I see your name very often and
learned also a lot from your answers on question from other users.

Best regards,
Gerrit

Even after I explained it to you? What part don't you understand?

--
Regards,
Tom Ogilvy

I would not use the FileSearch object to do this, you can just use
the Dir function:

Code:
--------------------

Sub test()

Dim strFound As String

strFound = Dir(Interaction.Environ$("userprofile") & "\My Documents\Test files\up12_7.txt")

If Len(strFound) > 0 Then
MsgBox "File found"
Else
MsgBox "File NOT found."
End If

End Sub
--------------------


HTH

TJ

Thanks TJ, your solution is working fine. But I still not understand
what was wrong with my solution...

Gerrit