Hello.
I found the attached code on Microsoft Community by Graham Mayor which searches a Word document (I'm using Office 365) for a keyword and outputs the sentence to Excel. It works really well. I was wondering if someone could extend the code so that it also outputs the line number and page number too - this would be really helpful
Thank you
Sub ExtractLine()
Dim oPara As Paragraph
Dim xlApp As Object
Dim xlBook As Object
Dim NextRow As Long
Dim oRng As Range
Dim strWord As String
Const strWorkbookname As String = "D:\My Documents\WorkbookName.xlsx"
strWord = InputBox("Enter word to find")
Set oRng = ActiveDocument.Range
With oRng.Find
Do While .Execute("^l")
oRng = vbCr
oRng.Collapse wdCollapseEnd
Loop
End With
On Error Resume Next
Set xlApp = GetObject(, "Excel.Application")
If Err Then
Set xlApp = CreateObject("Excel.Application")
End If
On Error GoTo 0
Set xlBook = xlApp.Workbooks.Open(Filename:=strWorkbookname)
xlApp.Visible = True
Set oRng = ActiveDocument.Range
With oRng.Find
Do While .Execute(strWord)
NextRow = xlBook.Sheets(1).Range("A" & xlBook.Sheets(1).Rows.Count).End(-4162).Row + 1
oRng.Start = oRng.Paragraphs(1).Range.Start
oRng.End = oRng.Paragraphs(1).Range.End - 1
xlBook.Sheets(1).Range("A" & NextRow) = oRng.Text
oRng.Collapse wdCollapseEnd
Loop
End With
End Sub
Bookmarks