Search multiple word documents for specific content and paste into excel.

    Evening all


    Almost complete novice at VBA so small words please and bare with me.


    I have been writing word documents for the past 3 months (approx 500) on safety legislation in my workplace.
    After completing all of these (which need updating regularly) I have been asked to reference the word documents to state which legislation is relevent to each word document.


    If possible what I need is for VBA to search a folder of word files for examples of text: eg. HOHOHO-1, HOHOHO-2 all the way to HOHOHO-600 (each word file contains around 5-6 random ones of these along with other information) and put them into a single spreadsheet. Each word file is named 01-file name, 02-file name etc to about 400. paste each file name into excel in row $a$ starting at a2, list the HOHOHO-XXX numbers down the left a$1$ and THEN to mark in the relevant cell which file name contains which HOHOHO-XXX numbers.


    Example:


    Word file '01-file name' contains, HOHOHO-1 HOHOHO-345, HOHOHO-567 and HOHOHO-789.


    spreadsheet would like something like the one attached.


    Any help is appreciated and would have me days of mucking about


    Thanks in advance![ATTACH=CONFIG]51241[/ATTACH]

    Re: Search multiple word documents for specific content and paste into excel.


    Try the example attached.


    You need to add the directory name in Cell B2. Click the button


    Rough and ready code (time constraints) and it needs improvement. It will be slow to process 500+ docs, but it will basically do what you want. Note all files in a directory will be scanned.


    Because of the range of columns potentially used, must run in XL2007+

    Re: Search multiple word documents for specific content and paste into excel.


    that's awesome thanks!


    the only problem is that it returns HOHOHO-4, HOHOHO-45 and HOHOHO-456 instead of just HOHOHO-456 and I'm struggling to get a [, wholeword] to work


    any suggestions?


    thanks

    Re: Search multiple word documents for specific content and paste into excel.


    It seems Word is disabling the 'Find Whole Word' option because of the '-'; it's assuming the search contains more than one Word...


    Hmm, when I get a chance later, I'll have a try for a workaround.

    Re: Search multiple word documents for specific content and paste into excel.


    If you have a .MatchWholeWord in there, remove it and replace with


    [indent]

    Code
    .MatchSuffix = True


    [/indent]

    Re: Search multiple word documents for specific content and paste into excel.


    Quote from cytop;648159

    If you have a .MatchWholeWord in there, remove it and replace with
    [INDENT]

    Code
    .MatchSuffix = True


    [/INDENT]


    Genius! Works perfectly


    Many thanks

    Re: Search multiple word documents for specific content and paste into excel.


    Hi,


    I'm totally new to VBA. I want something similar, but instead of listing out all file names in one row, I want to put them in one column, see below. Also, I only want files with the text1/text2, if no text1/text2 found in the file, then don't put the file name to the spreadsheet. Could you please kindly show me how to modify the code? Thank you in advance!!!
    [TABLE="width: 236"]

    [tr]


    [td][/td]


    [td]

    text1

    [/td]


    [td]

    text2

    [/td]


    [/tr]


    [tr]


    [td]

    filename1

    [/td]


    [td]

    x

    [/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    filename2

    [/td]


    [td][/td]


    [td]

    x

    [/td]


    [/tr]


    [tr]


    [td]

    filename3

    [/td]


    [td][/td]


    [td]

    x

    [/td]


    [/tr]


    [/TABLE]


    Re: Search multiple word documents for specific content and paste into excel.


    Welcome to OzGrid, Iriszhang.


    Please do not post questions in threads started by other users.


    Start your own thread, give it an accurate and concise title as required by the rules you agreed to when you joined and explain your issue fully. If you think this thread can help clarify your issue you can include a link by copying the URL from the address bar of your browser and pasting into your message.


    Thank you.

    Re: Search multiple word documents for specific content and paste into excel.


    Hi
    is there any chance to do exactly the same but to loop through excel files (all sheets and all folders and sub-folders)?


    thank you so much!

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login