2

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.

3
  • Do the documents include other items besides tables and dates? If so, how would you identify where the date is on the doc? Sep 14, 2013 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. Sep 14, 2013 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? Sep 15, 2013 at 11:43

1 Answer 1

1

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
2
  • In honest, I'm not sure what I'm looking at. I'm not sure how to achieve any of that. Sep 14, 2013 at 20:50
  • I put the specific vba code as the third line above, surrounded by your existing code.
    – Jim Mack
    Sep 15, 2013 at 19:48

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .