Results 1 to 4 of 4
  1. #1
    Join Date
    May 2003
    Thanked 1 Time in 1 Post

    Generate a list of DOC files that contain a key word

    I've spent forever searching for a replacement for application.filesearch. All results seem to relate to finding filenames containing key words - that has never been an issue for me. However, I DO have a macro which works in office 2003 (which we still have access to on some office computers) but of course won't work in office 2010.

    In Application.FileSearch I could use

    With .PropertyTests
    .Add _
    Name:="Text or Property", _
    Condition:=msoConditionIncludesPhrase, _
    Value:=Cells(F_Looper, 1).Value
    End With

    (obviously lifted from the full code, but it gives you the idea - I'm basically searching a series of directories and subdirectories for one or more key words or phrases).

    I work in an area that is responsible for making changes to system documents, and we therefore have to regularly find key words in documents to identify the scope. Whilst we CAN do this manually with windows find, that method is messy and means we have to work from screenshots which isn't exactly a smart way to go - especially as there are lots of development versions of the file - with the use of Excel and pivot-tables it's easy to screen out duplicate files and references!

    Any suggestions GRATEFULLY received!

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Pittsburgh, Pennsylvania, USA
    Thanked 342 Times in 335 Posts
    The Microsoft solution is at

    The typical choices for replacing Application.FileSearch in XL2007 and later are Scripting.FileSystemObject or DIR().

    To search for keywords within a file with either would require looping through the files, opening them in excel, and searching in them.


  3. #3
    Join Date
    May 2003
    Thanked 1 Time in 1 Post
    Yes, I should have mentioned that I've found solutions to loop through files - that's never been the issue - I do have working code that would open each and every found file and search for the key words too, BUT it has a serious speed overhead. The old method would typically take 12 plus hours to run, but that was fine as I could leave a machine logged on to the network and it would finish same day (eventually). With the open and check method it is much slower, and runs the risk of overnight network downtime - which means the macro never completes. IF that is the only solution, I'll have to live with it - though I hope someone can come up with an alternative. I'm ACTUALLY searching for Word documents, so this question would perhaps better have been in the VBA forum, it's in the Excel one because the macros and functionality is currently all excel based - but to be honest ANY method of getting an electronic list of files via code will work fine!

  4. #4
    WS Lounge VIP
    Join Date
    Mar 2002
    Newcazzle, UK
    Thanked 466 Times in 444 Posts
    In Word (and other Microsoft products), you can open a panel which allows various properties values to be set for the document (or file), including
    etc, in the Custom Tab, items such as
    etc etc.

    In Windows Explorer, where you browse to a folder, you typically see a file list, with a heading bar showing:
    Name; Date Modified; Type; Size

    If you right-click on this bar, you will see an option list which allows you to choose additional file information to be listed.
    If you click the 'More..." option from this option list, you will see a huge list of details which could be listed.
    And you can choose the order in which the file list is displayed in Windows Explorer, by dragging and moving the items in the header bar.
    So you could display the fileslisted in the browser window such as:
    Name; Date Modified; Size; Department; Comments;

    So, if your Users made use of the file property features, you could use this to have key words entered for the document.
    This would mean that you wouldn't need to open each doc for your search.
    It would then be very fast to create a file list using file scripting object (fso) methods.

    Just a thought.


Posting Permissions

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