file search with excel vba
 

I can do a file search using the following code:
Sub findfiles()


Application.MoveAfterReturnDirection = xlDown
Application.MoveAfterReturn = True


Set fs = Application.FileSearch
With fs
.NewSearch
.LookIn = "c:\"
.SearchSubFolders = True
.MatchTextExactly = False
.filename = "dssi expense.xlt"
If .Execute(msoSortByNone, msoSortOrderAscending,
True) 0 Then

'counts the files in list and stores the value in
total count
MsgBox "There were " & .FoundFiles.Count & _
" file(s) found."
totalcount = .FoundFiles.Count

'for each file found, write the path and file to a
cell.
For i = 1 To .FoundFiles.Count
Worksheets("sheet1").Cells(i, 1).Value
= .FoundFiles(i)
Next i


Else
MsgBox "There were no files found."
End If
End With

End Sub

The code is more or less borrowed from the various
samples. The problem is that the search finds all
templates using *.xlt, but it will not find specific
templates such as 'dssi expense report.xlt'. It will find
other specific templates, but not the one mentioned
above. If i create a new template, it will not find that
template, using a specific search. If i copy and paste
and existing template, that it has found before, it wont'
find the copy.

I have tried lots of test, but nothing is working. Any
ideas?

RobertW

file search with excel vba
 

Robert

Code seemed to work ok. I removed the msoSortByNone and
tried various combinations looking for full and part
names, templates and workbooks and it seemed to work. If
you are looking for the file "dssi expense report.xlt" you
won't find it using .filename = "dssi expense.xlt" but you
should find it using .filename = "dssi expense*.xlt".

Tony
-----Original Message-----
I can do a file search using the following code:
Sub findfiles()


Application.MoveAfterReturnDirection = xlDown
Application.MoveAfterReturn = True


Set fs = Application.FileSearch
With fs
.NewSearch
.LookIn = "c:\"
.SearchSubFolders = True
.MatchTextExactly = False
.filename = "dssi expense.xlt"
If .Execute(msoSortByNone, msoSortOrderAscending,
True) 0 Then

'counts the files in list and stores the value in
total count
MsgBox "There were " & .FoundFiles.Count & _
" file(s) found."
totalcount = .FoundFiles.Count

'for each file found, write the path and file to
a
cell.
For i = 1 To .FoundFiles.Count
Worksheets("sheet1").Cells(i, 1).Value
= .FoundFiles(i)
Next i


Else
MsgBox "There were no files found."
End If
End With

End Sub

The code is more or less borrowed from the various
samples. The problem is that the search finds all
templates using *.xlt, but it will not find specific
templates such as 'dssi expense report.xlt'. It will
find
other specific templates, but not the one mentioned
above. If i create a new template, it will not find that
template, using a specific search. If i copy and paste
and existing template, that it has found before, it wont'
find the copy.

I have tried lots of test, but nothing is working. Any
ideas?

RobertW

.