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?

Thanks

Answer
Answer

OK

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)
    oWord.ActiveDocument.Tables(1).Select
    oWord.Selection.Copy
    Activesheet.Paste R
    Set R = Cells(ActiveSheet.UsedRange.Rows.Count+1,1)
    oWord.ActiveDocument.Close False
  Next
  oWord.Quit
  Set oWord = Nothing
  ActiveSheet.Columns.Autofit

End Sub
   

Microsoft Excel MVP, Oxford, England. www.manville.org.uk

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: