import multiple word documents into one excel sheet

I have several word documents that have a few tables in them. I would like to get these tables into excel all in one worksheet at column A. Is there a way to import all of these documents into Excel or would it be better to try to merge all the documents and then copy and paste into excel?




So you could use a macro to copy the entire body of each of a set of documents into a single worksheet.  It would go something like this:

Sub GetWordDocContents()
  Dim oWord As Object
  Dim vFiles
  Dim iFile As Integer
  Dim R As Range
  vFiles = Application.GetOpenFilename("Word files (*.doc*),*.doc*",Title:="Please select the files you want to copy from", MultiSelect:=True)
  If TypeName(vFiles)="Boolean" Then Exit Sub   ' Cancelled
  Set oWord = CreateObject("Word.Application")
  oWord.Visible = True
  Set R = Worksheets.Add.Range("A1")
  For iFile = LBound(vFIles) To UBound(vFiles)  
    oWord.Documents.Open vFiles(iFile)
    Activesheet.Paste R
    Set R = Cells(ActiveSheet.UsedRange.Rows.Count+1,1)
    oWord.ActiveDocument.Close False
  Set oWord = Nothing

End Sub

Microsoft Excel MVP, Oxford, England.

6 people were helped by this reply


Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.


Question Info

Last updated September 2, 2020 Views 5,867 Applies to: