Using VBA to copy and paste text from word documents into excel

Question

I have an excel worksheet with a list of file paths to word documents (see attached screenshot). Each word document is a template with titles written in italics. I would like to extract from word into excel the text that follows the title 'discounting' (extraction should not include 'discounting'). The paragraph after discounting is usually titled 'pools and associations' (but not always so it would be helpful to have parameters that will end the extraction when it reaches a new italicized title). Below is what I have so far and the attached image is what the code yields in the column titled discounting. I have not been successful in adding a loop that will begin the process for the next file path in the second row. Also, as you can see in the screenshot, the title of 'discounting' is included in the extraction and the info that I need goes into the second row instead of the first (may have something to do with transpose?) I would like the paragraph in each word doc that is extracted to be in the same worksheet in the respective row next to the file path.

Sub CP_Discounting()

Dim WordDoc As String  

Dim objDoc As New Word.Document  

Dim Range As Word.Range  

Dim BeginText As String  

Dim EndText As String  

Dim WordApp As New Word.Application  

 

BeginText = "Discounting"  

EndText = "Pools and Associations"  

WordDoc = "P:\Opinions\Saved SAOs \1_AH_TAT.docx"  

 

Documents.Open WordDoc  

Documents(WordDoc).Activate  

 

Set objDoc = Documents.Application.ActiveDocument  

 

Set Range = Documents.Application.ActiveDocument.Content  

 

Range.Find.Execute FindText:=BeginText  

Range.MoveEndUntil Cset:="P", count:=wdForward  

     

Range.Copy  

 

Workbooks("2020 Compiling Data Macro_copy.xlsm").Sheets("Text mining").Range("E2").PasteSpecial Paste:=xlPasteValues  

WordApp.Quit  

 



 

End Sub