Find paragraph matching an expression in Word and exporting to Excel spreadsheet

ballpoint · #1 11-03-2017, 08:38 AM

Hi all,

I am looking to find a way to do the following, which I am not sure is even possible in Word / Excel. I tried to find a solution, but so far failed. Basically, I would need to:

Search for the instances in which a particular word / expression / regex occurs in
2) several word files in a folder
Find the matching paragraphs and copy them
Paste them in an excel spreadsheet

Ideally, the resulting spreadsheet would have the name of the document in the first column, the search operator I used in the second, and the matching paragraphs in the third. Something like this, to be clear:

Code:

Document | Operator | Text

Doc 1       | Operator1| Matching text
Doc 2       | Operator1| Matching text

Is there any way to accomplish any of that?

Thank you very much!

macropod · #2 11-03-2017, 08:30 PM

What you're describing is fairly straightforward, but you don't say whether there's just the one 'Operator' or multiple (but there seems to be an implication of multiple) , where that operator would be sourced, or whether the output workbook is a new one or an existing one and, if it's the latter, whether it's opened, closed or how it is to be identified.

ballpoint · #3 11-04-2017, 02:51 AM

Thank you very much. Your answer gives me some hope!

I apologise for my lack of clarity: that my VBA proficiency is really low is possibly quite an understatement.

The idea would be to have multiple search operators. These could be in a separate file, of course, such as a workbook or a text file, whichever is easier. The workbook to be created could be a new one or an extant one, whichever is easier, again. There is no real problem in merging them later, if need be. The only caveat is that the search would have to be carried out on some 400 Word documents.

Is this any clearer? Thank you so much!

macropod · #4 11-04-2017, 05:17 AM

Try the following Excel macro. It assumes the 'find' list is in column A of Sheet1 and, if you're using wildcards for a particular Find expression, you'll have TRUE in column B on that row.

The macro includes a folder browser, so all you need do is run the macro and select the folder to process. The macro will add a new sheet and output all matches it finds on that sheet.

You will need to set a Reference to the Microsoft Word Object Library in the Excel VBE.

Code:

Sub DataFind()
Application.ScreenUpdating = False
Dim strFolder As String
'Get the folder to process
strFolder = GetFolder
If strFolder = "" Then Exit Sub

Dim strFile As String, strFnd As String, strOut As String
Dim wdApp As New Word.Application, wdDoc As Word.Document
wdApp.Visible = True

Dim xlShtIn As Worksheet, xlShtOut As Worksheet, i As Long, j As Long
'Define Data sheet & create & initialize the output sheet
Set xlShtIn = Sheets("Sheet1"): Set xlShtOut = Sheets.Add: j = 1
xlShtOut.Range("A1").Value = "Document"
xlShtOut.Range("B1").Value = "Operator"
xlShtOut.Range("C1").Value = "Text"

'Process each document in the folder
strFile = Dir(strFolder & "\*.doc", vbNormal)
While strFile <> ""
  Set wdDoc = wdApp.Documents.Open(Filename:=strFolder & "\" & strFile, AddToRecentFiles:=False, Visible:=False)
  With wdDoc
    'Process each word from the list in the source sheet
    For i = 2 To xlShtIn.UsedRange.Cells.SpecialCells(xlCellTypeLastCell).Row
      strFnd = xlShtIn.Range("A" & i)
      With .Range
        With .Find
          .ClearFormatting
          .Replacement.ClearFormatting
          .Text = strFnd
          .MatchWildcards = (xlShtIn.Range("B" & i) = True)
          .Forward = True
          .Wrap = wdFindStop
          .Format = False
          .Execute
        End With
        'Send the matches to the output sheet
        Do While .Find.Found
          j = j + 1
          xlShtOut.Range("A" & j).Value = strFile
          xlShtOut.Range("B" & j).Value = strFnd
          xlShtOut.Range("C" & j).Value = Replace(Split(.Paragraphs(1).Range.Text, vbCr)(0), vbTab, " ")
          .Start = .Paragraphs(1).Range.End
          .Find.Execute
        Loop
      End With
    Next i
    .Close SaveChanges:=False
  End With
  strFile = Dir()
Wend

' Release object memory
wdApp.Quit
Set wdDoc = Nothing: Set wdApp = Nothing: Set xlShtIn = Nothing: Set xlShtOut = Nothing
Application.ScreenUpdating = True
End Sub

Function GetFolder() As String
Dim oFolder As Object
GetFolder = ""
Set oFolder = CreateObject("Shell.Application").BrowseForFolder(0, "Choose a folder", 0)
If (Not oFolder Is Nothing) Then GetFolder = oFolder.Items.Item.Path
Set oFolder = Nothing
End Function

ballpoint · #5 11-04-2017, 05:24 AM

Thank you so much! I'll try it ASAP and report back. It looks like it is going to solve my problem!

ballpoint · #6 11-04-2017, 12:32 PM

Ok, surely this is my fault, but I do not think this is working. I have set a reference to the the Microsoft Word Object Library. I have then tried to place some (simple) operators in column A (just a single word).

The macro runs, and Word is working, but eventually it just yields a new sheet with "Document | Operator | Text" in the first line, but nothing else.

I have tried tinkering with it, but I am at my wit's end.

Thank you!

macropod · #7 11-04-2017, 01:36 PM

Do you have your list of Find expressions in Column A of the worksheet named 'Sheet1'?

ballpoint · #8 11-04-2017, 02:02 PM

Quote:

Originally Posted by macropod

Do you have your list of Find expressions in Column A of the worksheet named 'Sheet1'?

Yes, I do. I have just tried with a simple word for the time being.

Thanks again!

gmayor · #9 11-04-2017, 10:08 PM

Paul's code anticipates a header row in the sheet with the word list

Code:

For i = 2 To xlShtIn.UsedRange.Cells.SpecialCells(xlCellTypeLastCell).Row

If you only have one word in your 'list' it will need to go in the second row of that sheet or there is nothing to search for.

ballpoint · #10 11-05-2017, 03:22 AM

Apologies, my explanation was not clear. I have done that too, yes, but it still yielded the same result. I have double checked the code, but honestly there is nothing I can see that is clearly wrong. Needless to say, I have tried with files that do contain the words I am searching for.

macropod · #11 11-05-2017, 04:03 AM

Did you actually select the folder to process? The code works fine for me as posted - I simply copied it from post #4 in to a new Excel workbook, added the Word reference and a search term, then ran the macro. The expected output was produced.

ballpoint · #12 11-05-2017, 04:20 AM

If by select you mean selecting the folder and clicking OK when prompted to do so, then yes, I have selected the folder. I am wondering if my running on Parallels might be the issue, but it should not, and I was having the same issue on a Windows desktop yesterday.

Of course, there might be a chance I am using it wrong. This is what it looks like at the start:

I don't doubt it is working properly for you, so I can only assume I am trying to do something wrong.

macropod · #13 11-05-2017, 04:30 AM

Quote:

Originally Posted by ballpoint

I am wondering if my running on Parallels might be the issue, but it should not

It could make all the difference in the world. The folder/path structure is completely different on Macs and I doubt the Dir() command would work there, either.

Quote:

Originally Posted by ballpoint

I was having the same issue on a Windows desktop yesterday.

All that suggests is an implementation error.

ballpoint · #14 11-05-2017, 05:24 AM

OK, I have just tried again from my Windows desktop and it works incredibly well! It was definitely the folder structure. Thanks a million! Now I just have to work on my regexes to find words close to each other, but beyond that I am set.

Thank you so much!

ballpoint · #15 11-05-2017, 03:49 PM

I am updating this for future reference. I have discovered that it works like a charm on Parallels too, so long as one takes care of selecting a folder within the C:\ drive of the Windows installation. Selecting a folder on, say, the desktop results in having to deal with the Mac folder structure (as macropod correctly observed). This, in turns, breaks the macro.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Exporting specific data fields from MS Word 2013 to a MS Excel 2013 spreadsheet	Labyrinth	Word	7	07-19-2016 01:35 PM
Exporting quiz scores from powerpoint to excel spreadsheet/word	rjagile	PowerPoint	1	02-08-2016 02:26 PM
Find and replace in word document repeated for every item in excel spreadsheet	Daniell	Word	1	02-17-2015 04:38 AM
Trying to import specific data from one spreadsheet to another, without matching rows	Wynka	Excel	0	11-26-2014 09:33 AM
Macro: Exporting Data to a LEGIBLE Excel Spreadsheet	jeffcoleky	Word VBA	6	05-08-2012 08:24 AM