Repeating macro with new term & find all word forms

[JordeeBec]

I found this macro to extract all the sentences containing a particular term into an Excel file: vbaexpress.com/kb/getarticle.php?kb_id=553

It works perfectly! But I want it to do more. I'd like for it to do exactly what it does above, then repeat the process for a different term, and put that into another sheet in the same Excel workbook (up to 10-20 terms/sheets).

Also, can I tell it to use the "find all word forms" search for some/all of the terms?

I'm working in Word 2007.

Thanks in advance!

[macropod]

The code in that link isn't very efficient but, more importantly for your purposes, where are the 'find' strings to be held (eg on the corresponding worksheets in the workbook, hard-coded, a document)?

[JordeeBec]

Hm. I'm really not picky. I'd be happy to have them in corresponding worksheets so it's easier to flip through them, but it might be better if they were compiled into one new document (i.e. all sentences with "smile," followed by all sentences with "nod," etc., in a single new document).

[macropod]

You may not be picky, but you need to make a decision - no-one can code for 'not picky'. If it's to be on the worksheets, we'd need to know the workbook's or document's name (and filepath unless it's always to be in the same folder as the document), plus details of how to identify the find strings (eg cell A1 on each worksheet, or a specified table in the Word document). Also, if the data are to be held in a Word document, how are the corresponding worksheets to be identified. You need to be quite specific about all the criteria.

[JordeeBec]

Sorry, I misunderstood. I think this might work best:

I'd planned to hard code the 'find' list into the macro myself, but if it'd be easier/better to have it run from a source list, we could put that in sheet one, column A of a workbook in a set location, C:\Work\wordlist.xlsx .

The source document would be a Word document.

The (new) destination document would be in the same folder as the current (source) document.

The (new) destination document would be a Word document (i.e. all sentences with "smile," hard page break, all sentences with "nod," hard page break, etc.).

Is that doable? Any other specifications I missed?

[macropod]

Sorry, but you're not making a lot of sense (to me at least). In your first post, you said you wanted the destination to be various sheets in an Excel workbook. Now you're referring to a destination document.

Try the 'GetData' macro in the attached workbook. It has three worksheets. On each worksheet, insert a word or string to find in cell A1 and, if it's a single word and you want to find all word forms, a 1 in B1. You can add more worksheets if you want. The macro uses a browser to select the folder of documents to be processed. The names of all the files processed will be output in Column A on each worksheet and, below the name entry in Column B, will be listed all the sentences in which the 'Find' word/string was found.

[JordeeBec]

Sorry, macropod, I should've clarified I was changing the output to a document (a workbook would still work, of course; I'm just gathering the data for some human analysis).

I wish I'd seen your reply a week ago! I spent half the day working on this macro yesterday. I ended up with this:

[VBA]Sub GrabbingCrutches()
'
' GrabbingCrutches Macro

Dim r As Range
Dim mystring As String
Dim ThisDoc As Document
Dim OtherDoc As Document

MsgBox "Remember to open a new document for the results, and close others!"

If Documents.Count <> 2 Then
MsgBox "Must have two (and only two!) documents open."
Exit Sub
End If



Set ThisDoc = ActiveDocument
If ThisDoc = Documents(1) Then
Set OtherDoc = Documents(2)
Else
Set OtherDoc = Documents(1)
End If

mystring = "nod"
Set r = ActiveDocument.Range
With r.Find
Do While .Execute(FindText:=mystring, MatchAllWordForms:=True, Forward:=True) = True
r.Expand Unit:=wdSentence
r.Copy
OtherDoc.Range.InsertAfter r.Text & vbCrLf
r.Collapse 0
Loop
End With

'cut and pasted the code from mystring through End With, changing the search term

End Sub
[/VBA]

I'm afraid it's not terribly elegant, either, but my attempts at pulling from a word list weren't successful.

The only other things I wish it would do would be to print the page number where the sentence was found after each sentence, and to add hard page breaks between the results for term 1 and term 2, etc.

I appreciate your time, Paul! Wish I'd seen this sooner; I would have totally used your solution if I had. Thanks again!

[macropod]

Given:

Wish I'd seen this sooner; I would have totally used your solution if I had

With my code, you can have the page numbers as well by inserting:
WkSht.Cells(LRow, 1).Value = .Duplicate.Information(3) 'wdActiveEndPageNumber
before:
WkSht.Cells(LRow, 2).Value = .Duplicate.Text
This will insert the page # in column A.

[mead jones]

Thanks

[JustRed]

Thank you!