Find text in multiple documents and export results.

[Griff389]

In excel 2003 there is a "file search" option that lets me search of text in multiple documents and then shows me a list of which documents contain the search term.

Is there a way to export the list of documents that the search returns. Or maybe an alternative?

Thanks

Carl

 

[fumei]

Yes. If you already have it showing you the list, then simply make a document of the list.

The list is .FoundFiles() of FileSearch, yes? Here is an example (note, running from Word):

Sub testFileSearch()
Dim StartLocation As String
Dim var
Dim NumberFiles As Long
StartLocation = “C:\VBAClass”
With Application.FileSearch
   .NewSearch
   .LookIn = StartLocation
   .FileName = "*.doc"
   .FileType = msoFileTypeAllFiles
   .SearchSubFolders = True
   .TextOrProperty = "Key word"
End With

NumberFiles = Application.FileSearch.Execute()
[b]For var = 1 To NumberFiles[/b]
      Selection.TypeText _
         Text:=[b]Application.FileSearch.FoundFiles(var)[/b] & _
            vbCrLf
Next var
End Sub

What does this do?

This types the full path and file name for every file found that has the extension “.doc”, AND contains the text string “Key word” – followed by a paragraph mark.

EXAMPLE ONLY!
C:\Blah\ThisOne.doc
C:\Blah\ThisOtherOne.doc
C:\Blah\YetAnotherOne.doc
C:\Blah\SubFolderA\Some.doc
C:\Blah\SubFolderA\SomeOther.doc
C:\Blah\SubFolderA\SubSubFolderB\Whatever.doc

So if you are going to "export" the list - and export where??? - you could do something like the above.

faq219-2884

Gerry
My paintings and sculpture

 

[Griff389]

Sorry for the late reply.

Thanks for that. I'll take a look and see if I can make something from that.

A user just wants a list of all the files that contain the text he's looking for, so hopefully this will do the trick.

Cheers