PDA

View Full Version : Assist in developing new procedure: Lists.



YellowLabPro
03-13-2007, 03:03 AM
I found this program by XLGibbs in the KB, and is very close to the idea I want, but not exactly:
http://vbaexpress.com/kb/getarticle.php?kb_id=837

I want to search for files, either by name or by extension and then create this list in Excel.

Example:
Search for all .xls files in C:\My Documents
Results would list them alphbetically in a new workbook

The difference in this procedure and XLGibbs is rather than just selecting the location to search, there is an option to filter by name as in a Windows Key word search, using "*".

Thnks,

YLP

mdmackillop
03-13-2007, 06:29 AM
Just add an input box where you can insert the file name or extension. It's not foolproof, as for example "doc" could appear as both part of the name or the extension. You're probably okay with XLS though.
To do this properly, you could add a simple userform to create the search string with the right syntax. *doc*.xls or *.doc


With Application.FileSearch
.Filename = InputBox("Enter file name or extension")

Charlize
03-13-2007, 06:46 AM
I use this one to search for files in c:\data. I can enter a keyword to use and the value for this keyword.Sub Look_For_Keyword_In_File()
Dim fs As FileSearch
Dim Keyword_Phrase As String
Dim Keyword_Lookvalue As String
Dim messagetext As String
Dim i As Long
Set fs = Application.FileSearch
fs.NewSearch
Keyword_Phrase = InputBox("Caption of keyword :")
Keyword_Lookvalue = InputBox("What are you looking for :")
With fs
With .PropertyTests
.Add _
Name:=Keyword_Phrase, _
Condition:=msoConditionIncludesPhrase, _
Value:=Keyword_Lookvalue
End With
.LookIn = "C:\Data"
.SearchSubFolders = False
.filename = "*.*"
.MatchTextExactly = False
.FileType = msoFileTypeAllFiles
.Execute
End With
If fs.Execute() > 0 Then
MsgBox "There were " & _
fs.FoundFiles.Count & _
" file(s) found."
For i = 1 To fs.FoundFiles.Count
messagetext = messagetext + fs.FoundFiles(i) + vbCrLf
Next i
MsgBox "Found files : " & vbCrLf & messagetext, vbOKOnly, "Keyword : " & _
Keyword_Phrase & " / Lookupvalue : " & Keyword_Lookvalue
Else
MsgBox "There were no files found."
End If
End SubCharlize