|
|
|
|
|
|
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
|
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"
.Execute
If .Found Then
aRange.Expand Unit:=wdSentence
aRange.Copy
aRange.Collapse wdCollapseEnd
If objSheet Is Nothing Then
Set appExcel = CreateObject("Excel.Application")
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:
|
- Open a new Word Document and go to Tools-Macro-Visual Basic Editor
- With the VBE now open, click on insert-module.
- Paste the code above into the module.
- Change the code to match the location of your test.xls
- Close the VBE by clicking on the X in the upper right hand corner of the window.
|
Test the code:
|
- run the macro by going to tools-Macro-Macro's and selecting the FindWordCopySentence macro.
- 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.
|
|