In column A of the spreadsheet I'm trying to extract the data after a key word, and then column B, the name of the file.
In column A I only get the first letter and column B is working fine. With the below, I'm trying to extract, My Data is stored here in column A.
1.1.1. Source of Count. My Data is stored here.
Also, in some documents, there may be multiple instances of Source of Count. I would like to capture those also. I supposed the file name would have to go in Column A with the results beginning in Column B and to the right.
Code: Select all
Sub ExtractInfo() Dim myPath As String Dim myFile As String Dim myText As String Dim xlRow As Long Dim xl As Object: Set xl = CreateObject("excel.application") xl.Workbooks.Add xl.Visible = True myPath = "C:\Users\admin\Library\" myFile = Dir(myPath & "*.docx") xlRow = 1 Do While myFile <> "" Documents.Open myPath & myFile Selection.HomeKey Unit:=wdStory With Selection.Find .ClearFormatting .MatchWildcards = True .Text = "Source of Count. [<A-Z.]" .Execute End With Selection.MoveStart Count:=Len("Source of Count. ") myText = Selection.Text xl.ActiveWorkbook.ActiveSheet.Cells(xlRow, 1) = myText xl.ActiveWorkbook.ActiveSheet.Cells(xlRow, 2) = myFile xlRow = xlRow + 1 ActiveWindow.Close False myFile = Dir Loop End Sub