I have about 4000 doc and docx files which contain a table. I've managed to import these into one excel sheet using the following script:
Sub Macro1() Dim xl As Object Set xl = CreateObject("excel.application") xl.workbooks.Add xl.Visible = True 'Here put your path where you have your documents to read: myPath = "C:\Users\" 'End with '\' myFile = Dir(myPath & "*.docx") xlRow = 1 Do While myFile <> "" Documents.Open FileName:=myPath & myFile, ConfirmConversions:=False, _ ReadOnly:=False, AddToRecentFiles:=False, PasswordDocument:="", _ PasswordTemplate:="", Revert:=False, WritePasswordDocument:="", _ WritePasswordTemplate:="", Format:=wdOpenFormatAuto, XMLTransform:="" xlCol = 0 For Each t In ActiveDocument.Tables For Each r In t.Rows For Each c In r.Range.Cells myText = c myText = Replace(myText, Chr(13), "") myText = Replace(myText, Chr(7), "") xlCol = xlCol + 1 xl.activeworkbook.activesheet.Cells(xlRow, xlCol) = myText Next c xlRow = xlRow + 1 xlCol = 0 Next r Next t ActiveWindow.Close False myFile = Dir Loop xl.Visible = True End Sub
The only issue is that outside the table of the docs is a date. Because this isn't in the table, it doesn't pick it up and I have a huge list of data with no dates. How can I get it to import ALL the data or at least, the date into the excel sheet also. Without dates the data I have could be in any order and useless to me.