file search with excel vba

  • Thread starter Thread starter RobertW
  • Start date Start date
R

RobertW

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
 
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
 
Back
Top