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" <werner-rohrmoser@hotmail.de> wrote in message
news:1135676108.889787.266550@g49g2000cwa.googlegroups.com...
> 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
>

[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

"Werner Rohrmoser" <werner-rohrmoser@hotmail.de> wrote in message
news:1135753154.133159.210000@z14g2000cwz.googlegroups.com...
> 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
>

[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" <werner-rohrmoser@hotmail.de> wrote in message
news:1135753154.133159.210000@z14g2000cwz.googlegroups.com...
> 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
>

[Werner Rohrmoser]

Bob,

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

Werner