Word

Extract sentences containing a specific word to excel file

Ease of Use

Easy

Version tested with

2000, 2002, 2003 

Submitted by:

lucas

Description:

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

Discussion:

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. 

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