Extract sentences with a specific word & page number from Word into Excel

Samantha4183 Use the following in which the changes to your original code are shown in red.

Const t = "sunshine" ' the text to search for
Dim s As Range
Dim xl As Object
Dim wb As Object
Dim ws As Object
Dim r As Long
Dim p As Long
Set xl = CreateObject("Excel.Application")
Set wb = xl.Workbooks.Add(Template:=-4167) ' xlwbatworksheet
Set ws = wb.Worksheets(1)
For Each s In ActiveDocument.Sentences
If InStr(1, s.Text, t, vbTextCompare) > 0 Then
p = s.Information(wdActiveEndPageNumber)
r = r + 1
ws.Cells(r, 1).Value = s.Text & " from page " & p
End If
Next s
xl.Visible = True

Samantha4183 

The following is from ChatGPT in Edge. I have no clue whether it works.

https://www.bing.com/search?form=MY0291&OCID=MY0291&q=Bing+AI&showconv=1

========Start of AI response ===============================

To extract all the sentences containing a specific word from a Word document using VBA from Excel, you can use the following steps:

1. Open your Excel workbook.
2. Press Alt + F11 to open the Visual Basic Editor.
3. Click on “Insert” in the top menu bar, then click on “Module”.
4. Copy and paste the following code into the new module:

Sub ExtractSentences()
    Dim objWord As Object
    Dim objDoc As Object
    Dim objRange As Object
    Dim objExcel As Object
    Dim objSheet As Object
    Dim strWord As String
    Dim lngPageNumber As Long
    Dim lngRow As Long
    
    'Set the word to search for
    strWord = "your_word"
    
    'Create a new instance of Word
    Set objWord = CreateObject("Word.Application")
    
    'Open your Word document
    Set objDoc = objWord.Documents.Open("C:\Path\To\Your\Document.docx")
    
    'Create a new Excel workbook and worksheet
    Set objExcel = CreateObject("Excel.Application")
    Set objSheet = objExcel.Workbooks.Add.Sheets(1)
    
    'Loop through each page in the document
    For lngPageNumber = 1 To objDoc.ComputeStatistics(2)
        'Go to the beginning of the page
        Set objRange = objDoc.GoTo(1, 1, lngPageNumber)
        
        'Loop through each sentence on the page
        Do Until objRange.End >= objDoc.Range.End
            If InStr(objRange.Text, strWord) > 0 Then
                'Copy the sentence to Excel
                lngRow = lngRow + 1
                objSheet.Cells(lngRow, 1).Value = objRange.Text
                
                'Add the page number to Excel
                objSheet.Cells(lngRow, 2).Value = lngPageNumber
            End If
            
            'Move to the next sentence
            Set objRange = objRange.Next(7)
        Loop
    Next
    
    'Close Word and Excel without saving changes
    objDoc.Close False
    Set objDoc = Nothing
    
    objWord.Quit False
    Set objWord = Nothing
    
    objExcel.Quit False
    Set objExcel = Nothing
    
End Sub

1. Replace “your_word” with the word you want to search for.
2. Save and close the module.
3. Press F5 to run the macro.

This macro will loop through each page in your Word document and extract all sentences containing your search word into a new Excel workbook. The page number of each sentence will also be included in the Excel workbook.

I hope this helps!

===========End of AI response ===========================

This is the question I posted:

I have a very large Word document where a specific word appears hundreds of times. Using vba from Excel, I would like to extract all of the sentences where the word appears into Excel, including the page number the sentence is found on.

Note that many in the know say that "Artificial Intelligence" actually means "Artificial Stupid."