Word text search from within Excel?

Ed Nerd · Sep 17th 2003

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

**Andy Pope** · Sep 18th 2003

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

Ed Nerd · Sep 19th 2003

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

**Andy Pope** · Sep 22nd 2003

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

Ed Nerd · Sep 23rd 2003

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

**Andy Pope** · Sep 23rd 2003

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

Ed Nerd · Sep 23rd 2003

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

**Andy Pope** · Sep 23rd 2003

Hi Ed,

This particular button is from the Forms toolbar.
Go thru the XL menus View > Toolbars > Forms.

Right click the Button to Format and assign a macro.

Cheers
Andy

Ed Nerd · Sep 23rd 2003

Got it! Thank you!

Ed

456 · Oct 1st 2003

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

**Andy Pope** · Oct 2nd 2003

This is a slightly modified version.

The search text is encased in quotes (") if you request an exact match.

Cheers
Andy

456 · Oct 2nd 2003

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:

Code

Option Explicit


Function searchin(trainnumber As String, carnumber As String) As Integer
    Dim search_dir As String
    Dim searchFor As String
    Dim strQuote As String
    strQuote = """"


    search_dir = "c:\its456\Dick\" 
    searchFor= strQuote & trainnumber & "/" & carnumber & strQuote
    
    With Application.FileSearch
        .NewSearch
        .LookIn =seacrh_dir
        .SearchSubFolders = False
        .MatchTextExactly = True
        .TextOrProperty = searchFor
        .Filename = "*.doc"
        .Execute
        searchin = .FoundFiles.Count
    End With
End Function

Display More

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]

**Andy Pope** · Oct 2nd 2003

I'm glad you got it working.

But just to confirm, in my non working example,
you did change the contents of the Match cell, D11, to exactly.

Cheers
Andy

456 · Oct 2nd 2003

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]

**Andy Pope** · Oct 3rd 2003

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

Word text search from within Excel?

Participate now!

Share

Users Viewing This Thread