Directory Documents keyword Search

alan.temple@hok.com

New Member
Joined
Mar 10, 2002
Messages
2
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
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,492
Messages
6,113,967
Members
448,537
Latest member
Et_Cetera

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top