Word text search from within Excel?

  • In my spreadsheet, I have links to many Word docs. There is often a need to
    search through the Word docs for those containing certain words or phrases.
    This is easily done from within Word. But can I call that Word function up
    from within Excel and use it? More specifically, can I "capture" this
    within my spreadsheet, and allow my users to access this search function,
    regardless of their individual Word and Excel settings? (Assume macros ON,
    Word and Excel 2000 on Win2000.)


    Thanks for any and all help.


    Ed

  • Hi Ed and welcome,


    I have read your post many times and can not quite workout what you want.


    Do you want to open a word document or all word documents and then display the search dialog?


    Do you want to mimic the word search functionality But on the contents of your workbook?


    If you use hyperlinks to the word files they would be opened simply by clicking the link.
    Then the user could use Word in the usual way. Making use of copy and paste.


    Maybe you could expand and clarify.


    Cheers
    Andy

  • Thanks for replying, Andy.


    I'm working with engineering reports - we're up to about 7900 and growing. I've created a spreadsheet that tracks these reports. Using the results of queries to our database, I extract several parameters and list them in the columns of the spreadsheet to allow the engineers to filter for specific items with specific characteristics.


    I have also extracted the narrative portion of each of these reports, saved each as a separate Word document, and provided a link to the corresponding Word file in my spreadsheet.


    Often, though, the parameters I've included on my spreadsheet don't provide enough information to filter. For instance, a certain engineer may be concerned about a high failure rate on shafts made of a certain metal compound. To find that, he would have to filter the spreadsheet several different times to include all possible systems using that type of shaft, then read each narrative to see if it included his item of interest.


    It would be easier to go to Word and, using the Open>Tools>Find dialog box, search all the narratives for "shaft", "steel-graphite", "failed"; that returns all the documents of interest regardless of the item parameters I've included - or not - on my spreadsheet.


    But most of these people are not Word-savvy, not to the point of using that type of search. I'd like to provide it to them in a macro from within my Excel spreadsheet, since that's what they'll have open.


    I thought of one possible way:
    -- Set up certain cells in which the engineers would enter their search terms
    -- Create a command button that would dim those cell values as strings and open a Word document
    -- The Word doc would have an On_Open event File Search macro that would use my dimmed strings as search parameters.


    That would put them into Word with a list of all files meeting their criteria.


    Is this feasable? Is there a better way? If this is good, how do I get my strings dimmed in Excel across to the Word macro?


    All help is greatly appreciated.
    Ed

  • Hi Ed,


    Quite a large project you have in mind :)
    So lets take this a small part at a time.


    Try this example, using file search.


    I tested this using plain text files. So change the filename property to *.doc to process word files.


    Change the file location and as needed.


    Post back for clarification if neccessary.


    Cheers
    Andy

  • Andy, this is *AWESOME*!! Beats the heck out of what Iwas trying to do! (My best solution was to code the FileSearch in Word, put it in a document as a Doc_Open event, the hyperlink to that doc in my spreadsheet.)


    Three questions, if I may:


    (1) How do I make it search for "this" AND "that" AND "the other", rather than OR?


    (2) What does the Macro 1 in Module 2 do?


    (3) If I take this sheet, Select All, Copy, and Paste into my existing workbook, will all the code come with it? (I assume I would have to move the code modules from This Workbook into Sheet1.)


    Again, my most spectacular thanks! :tumble::spin:


    Ed

  • Hi Ed,


    Glad you like it :)


    To answer your questions;
    (1) The cells inbetween Keywords can contain the value or / and. Click in D6 and you should see the two choices.


    (2) Ignore this, used to generate the code for creating hyperlinks.
    See, even I use the macro recorder :biggrin:


    (3) No, the code will not be copied this way.
    Go to the VBE (ALT+F11)
    In the project window you can drag and drop the module between workbooks.
    You wil need to revise the OnAction of the button on the worksheet to use the correct macro.


    Cheers
    Andy

  • Andy - if you don't mind, I need to ask - how did you create the button? I can't get a toolbox to show up for a button unless I do a UserForm. Or am I looking in the wrong direction?


    Ed

  • Hi Andy,


    Thanks for the attached file! At this moment i have EXACTLY the same problem
    B U T:
    It doesn't work quite well for me.
    Because the macro returns a result as well, while there isn't a founded string! I am absolutely sure about that.
    e.g.
    I'd like to use this function from windows:
    http://stuwww.uvt.nl/~s990868/untitled1.jpg


    Now i would like to find the combination of: "9316/4538" in some *.doc files.
    Windows will return a file which is shown at arrow 2. Your macro does the same. So far so good.
    But the combination "9316/4539" is
    NOT in the document. Via the function as shown above, windows will not return 1 file. BUT your code will do it! Your code will return the same document as shown above. Weird or not?
    Do you know what i do wrong?
    (does this function not accomodate my problem?)
    Tnx!


    EDIT:
    I see what the problem is/can be. The sign '/' is seen as 'and'.
    What i like to search is the string '1234/5678' EXACTLY. so WITH the '/' sign.
    At this moment, he is searching for 1234, and if it is found, he will search for the next string, 5678 somewhere else in the document.
    Any idea how i can put the search string litterally in .TextOrProperty?
    tnx anyway :)

  • I have to cry a little... This is it! Those quatriple quotes!
    I have to say that you code did not work, but i wrote a function, where your code was the basis of:


    But this works. It only find the correct string, like '1111/2222' which is an unique number!
    Thanx man! (it is soooooooo simple after all ;) )


    [solved]

  • Jup. I was wondering why in n2 the word exactly stood. I didn't saw it in the popdown list just under the first textfield.
    Later i noticed that there was an extra match field!


    But at this moment, i already found the problem: You was working with FileSearch.Filename = "*.txt". But my documents are all *.doc. In my own code, i allready had *.doc, so thats why it was working with me (with the extra quotes)
    BTW, i didn't REALLY test it, but the results are the same, if i set 'exactly' or not :)
    Tnx andy!
    [bit offtopic]
    Now i want to load this function in its memory every time excel starts. is that possible (like =sum, or =if etc)? Or do i have to run the module, where the function is in, in a excel sheet.
    [/bit offtopic]

  • To make it available in the formula bar use need to do one of the following


    save the function in your personal.xls
    save the workbook containing the function in the xlstart folder. probably need to save as .xla or at least set the property of Thisworkbook to be IsAddin.
    create an addin.


    keep an eye on this current thread


    Cheers
    Andy

Participate now!

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