Extract sentences containing a specific word to excel file

Ease of Use


Version tested with

2000, 2002, 2003 

Submitted by:



Extracts each sentence with the word "shall" in a Word Document to a new line in an Excel File 


I would like to extract all sentences from a Word Doc that contain the word "shall". This script will extract each sentence to a new line in an excel file located in C:\Temp\ The location of the Excel file can be changed in the code. The word to search for to decide which sentences to extract can be changed in the code. 


instructions for use


' Put this code in a module: Option Explicit Sub FindWordCopySentence() Dim appExcel As Object Dim objSheet As Object Dim aRange As Range Dim intRowCount As Integer intRowCount = 1 Set aRange = ActiveDocument.Range With aRange.Find Do .Text = "shall" ' the word I am looking for .Execute If .Found Then aRange.Expand Unit:=wdSentence aRange.Copy aRange.Collapse wdCollapseEnd If objSheet Is Nothing Then Set appExcel = CreateObject("Excel.Application") 'Change the file path to match the location of your test.xls Set objSheet = appExcel.workbooks.Open("C:\temp\test.xls").Sheets("Sheet1") intRowCount = 1 End If objSheet.Cells(intRowCount, 1).Select objSheet.Paste intRowCount = intRowCount + 1 End If Loop While .Found End With If Not objSheet Is Nothing Then appExcel.workbooks(1).Close True appExcel.Quit Set objSheet = Nothing Set appExcel = Nothing End If Set aRange = Nothing End Sub

How to use:

  1. Open a new Word Document and go to Tools-Macro-Visual Basic Editor
  2. With the VBE now open, click on insert-module.
  3. Paste the code above into the module.
  4. Change the code to match the location of your test.xls
  5. Close the VBE by clicking on the X in the upper right hand corner of the window.

Test the code:

  1. run the macro by going to tools-Macro-Macro's and selecting the FindWordCopySentence macro.
  2. Click run and the macro will copy all sentences with the word "shall" to your test.xls

Sample File:

Extract to excel file.zip 12.09KB 

Approved by mdmackillop

This entry has been viewed 240 times.

Please read our Legal Information and Privacy Policy
Copyright @2004 - 2020 VBA Express