question

DearingDelaneyWeatogue-7277 avatar image
0 Votes"
DearingDelaneyWeatogue-7277 asked emizhang-msft edited

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

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.


116177-capture.png

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


office-vba-dev
capture.png (21.5 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

0 Answers