Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel File search function | Excel Discussion (Misc queries) | |||
File Search Excel 2003 | Excel Discussion (Misc queries) | |||
how do you search for a file in Excel 07? | Excel Discussion (Misc queries) | |||
How to search for a value in a column in another Excel file | Excel Discussion (Misc queries) | |||
How to use one excel file to search another? | Excel Discussion (Misc queries) |