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.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

   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.

  • Do the documents include other items besides tables and dates? If so, how would you identify where the date is on the doc? – Raystafarian Sep 14 '13 at 9:57
  • The document includes a top table, this contains information like revision number, page number, company name and date the form was made. All of which is not what I require. Beneath that is a line of text, the date and beneath that is the table which I want. The filenames also contain the date if that helps. – Darkstar.Industries Sep 14 '13 at 20:11
  • 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? – Raystafarian Sep 15 '13 at 11:43

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
        xl.activeworkbook.activesheet.Cells(xlRow, xlCol + 1) = myFile
        xlRow = xlRow + 1
  • In honest, I'm not sure what I'm looking at. I'm not sure how to achieve any of that. – Darkstar.Industries Sep 14 '13 at 20:50
  • I put the specific vba code as the third line above, surrounded by your existing code. – Jim Mack Sep 15 '13 at 19:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.