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