Copying data from multiple word docs into one excel sheet

Question

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.

Solution

Your comment indicates the file name includes the date you want. You are accessing the filename in the variable myFile. Why not chop that apart to get the date, and insert it somewhere you want? You could make it the first or last cell in the destination table, for example.

Example added:

Try adding this new row to your script.

        Next c
        'new
        xl.activeworkbook.activesheet.Cells(xlRow, xlCol + 1) = myFile
        xlRow = xlRow + 1

Comments

[Raystafarian]

You already have the file name stored in MyFile so you could just assign the first cell that value and then import the table, for each doc, right?

[Darkstar.Industries]

In honest, I'm not sure what I'm looking at. I'm not sure how to achieve any of that.

[Jim Mack]

I put the specific vba code as the third line above, surrounded by your existing code.