FileSearch using FileType

[Werner Rohrmoser]

Dear all,

I've read some articles in this group, where is reported that
FileSearch has some problems, but I didn't found my problem:

I use Excel XP SP3 with WIN XP SP1.

When I use ".FileType = msoFileTypeExcelWorkbooks" then
I get not only the XL-files, I get all other files (*.doc, *.txt, etc.)
as well.

Is there anything special what I have to observe?

Thanks for your contributions
Werner

 

[Bob Phillips]

Werner,

In my tests that works fine. Can you show us the full code?

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Werner Rohrmoser]

Bob,

thanks for your reply and see code below:
what I have found out is, that in combination with
".FileName = "IB*.*"" it doesn't work.

***************************************************************************************************
Option Explicit
Option Compare Text

Public Sub FindXLSFiles()

Dim FileList() As String
Dim FileCount As Long
Dim LoopCounter As Long

With Application.FileSearch
.NewSearch
.LookIn = "D:\SPO\BNCHMARK\Data\Out"
.FileName = "IB*.*"
.SearchSubFolders = False
.FileType = msoFileTypeExcelWorkbooks
If .Execute(SortBy:=msoSortByFileName,
SortOrder:=msoSortOrderAscending) = 0 Then Exit Sub
ReDim FileList(.FoundFiles.Count)
For FileCount = 1 To .FoundFiles.Count
ListOfFiles.Cells(FileCount, 1).Value = .FoundFiles(FileCount)
Next FileCount
End With

End Sub
'*******************************************************************************************

Regards
Werner

 

[NickHK]

Werner,
Can't say I use .FileSearch, but what if you use "IB*.xls" as your .FileName
?

NickHK

 

[Bob Phillips]

Werner,

How strange that is. It seems that by using a wildcard, the filetype is
ignored. No matter what I tried in removing the .*, re-ordering the
parameters, didn't work. The inly thing that I could get to work was by
using IB*.xls.

Public Sub FindXLSFiles()

Dim FileList() As String
Dim FileCount As Long
Dim LoopCounter As Long

With Application.FileSearch
.NewSearch
.LookIn = "D:\SPO\BNCHMARK\Data\Out"
.Filename = "IB*.xls"
.SearchSubFolders = False
.FileType = msoFileTypeExcelWorkbooks
If .Execute(SortBy:=msoSortByFileName, SortOrder:=msoSortOrderAscending)
= 0 Then Exit Sub
ReDim FileList(.FoundFiles.Count)
For FileCount = 1 To .FoundFiles.Count
ListOfFiles.Cells(FileCount, 1).Value = .FoundFiles(FileCount)
Next FileCount
End With

End Sub


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Werner Rohrmoser]

Bob,

bottom line:
I have to take care using .FileName and .FileType together.
Thanks.

Werner