Directory Documents keyword Search
Amazing chart utilities from Jon Peltier
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 4 of 4

Thread: Directory Documents keyword Search

  1. #1
    New Member
    Join Date
    Mar 2002
    Location
    Tampa, Florida
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    I have a worksheet with keywords in row "2" beginning at column "B" extending to column "CB". In Column "A" I have a list of document file names beginning in row "4" continuing down as many rows as is needed to list all the documents in the file.

    I would like a macro that searches each of the files listed in Column "A" for the keywords listed in row "2", and then place the number "1" in the intersecting cell of the file name and the keyword when the keyword is found in the document being searched. Example: search file E:FSU

  2. #2
    MrExcel MVP Damon Ostrander's Avatar
    Join Date
    Feb 2002
    Location
    Denver, Colorado USA
    Posts
    4,240
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Alan,

    You can do this using the Office FileSearch object. In particular, see the PropertyTests Collection object in the VBA helps. You will just need a macro that loops through each filename and keyword, and runs the FileSearch on it. Be aware that a keyword search only searches for names that are explicitly identified as keywords in the file properties keywords field. If you want to search for a word anywhere in a file, you need to search the Contents of the files rather than the Keywords. If after checking this out if you are not sure how to apply it to your problem, respond back and I'll give you a bit of code.

    Damon
    Keep Excelling.

    Damon

    VBAexpert Excel Consulting
    LinkedIn Profile http://www.linkedin.com/pub/damon-ostrander/7/79/a93
    AllExperts Profile http://www.allexperts.com/ep/1059-30...-Ostrander.htm

  3. #3
    Guest

    Default

    Exclude Hidden Text from word search:

    Thanks Damon for your assistance! I haven’t had cause to get into the wide world of word VB until now. Your response took me on quite a tour. I have the search process running nicely now. However, I realize that many documents I need to search contain hidden text, which I would like to exclude from the search. I have attempted a few “ Not hidden: statements and a few “PropertyTests” to no avail. I would greatly appreciate your further assistance.

    As a side bar comment, the “PropertyTests” help file, needs help. Neither the word or excel help files provide a great deal of help information on this subject. The various condition options are not self explanatory and I could not find them defined in the help file, and MSN was of no help either.

    Have a great day

  4. #4
    New Member
    Join Date
    Mar 2002
    Location
    Tampa, Florida
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    Exclude hidden text from search?

    One last issue and it done! Many of the MsWord Documents I need to search contain Hidden text. I need to exclude the hidden text from the word search. I have attempted everything I can to no avail. Could you give me a lead or better yet reply with a code lines to do the job, please!

    Here is what I have developed and it works nicely. However

    Dim fs As Object
    Set fs = Application.FileSearch
    Dim MyColumn As Integer
    Dim MyColumn2 As Integer
    Dim SearchWord As String
    Dim SearchFile As String
    Dim MyPosition As Integer
    Dim c As Object
    Dim SearchRow As Integer
    MyRow = Range("A1").Value
    MyColumn2 = Range("A3").Value 'Contains the formula column() so the program knows what the last column is
    With ActiveSheet
    Range("A3").Select ‘Selects first row, column “A” above the Searchfile list
    MyColumn = 0
    With fs
    Do While MyColumn < MyColumn2 - 2 'This loops through all the SearchWords in row 2, beginning at column “B”
    MyColumn = MyColumn + 2
    SearchWord = Range(Cells(2, MyColumn), Cells(2, MyColumn)).Value
    Do While ActiveCell.Row < MyRow - 2 'This loop goes thru the file list in column A
    ActiveCell.Offset(1, 0).Select
    SearchFile = ActiveCell.Value
    If SearchFile = "" Or IsEmpty(SearchFile) = True Then
    Else
    .NewSearch
    .LookIn = SearchPath ‘SearchPath is provided by the Explorer browser
    .SearchSubFolders = True
    .TextOrProperty = SearchWord
    .MatchAllWordForms = True
    .FileType = msoFileTypeAllFiles
    .Execute
    For i = 1 To fs.FoundFiles.Count
    MyPosition = InStr(1, fs.FoundFiles(i), SearchFile, 1)
    If MyPosition > 0 Then 'If the Search file (which is the file name listed in column A is found _
    in the array of files containing the Searchword then its position _
    within the string will be greater than 0 and therefore it exists as a _
    part of the arrray
    With Range(Cells(4, 1), Cells(MyRow - 1, 1)) 'Address of the file list on the worksheet
    Set c = .Find(SearchFile, LookIn:=xlValues)
    If Not c Is Nothing Then
    SearchRow = c.Row
    Range(Cells(SearchRow, MyColumn), Cells(SearchRow, MyColumn)).Value = 1
    End If
    End With
    End If
    Next
    End If
    Loop
    Range("A3").Select
    Loop
    End With
    End With

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com