title: Extract tables from multiple files to Excel using VBA
body: <p>I have no experience with VBA as I usually use Matlab or sometimes Python, but it seems like it is the most useful tool for a project I have. Basically from a large number of Word files, I have to extract a table and put it into a single Excel file. 
  From a YT tutorial, I already have the following basic code:</p>
  
  <pre><code>Sub CopyTable()
  Application.Templates.LoadBuildingBlocks
  Dim xlApp As Excel.Application
  Dim xlwb As Excel.Workbook
  
  Dim doc As Document
  Dim tbl As Table
  Dim LastRow As Long, LastColumn As Integer
  Dim tblRange As Range
  
  Set doc = ThisDocument
  
  Set xlApp = CreateObject("Excel.Application")
  xlApp.Visible = True
  Set xlwb = xlApp.Workbooks.Add
  
  Set tbl = doc.Tables(3)
  With tbl
  LastRow = .Rows.Count
  LastColumn = .Columns.Count
  
  Set tblRange = .Cell(1, 1).Range
  tblRange.End = .Cell(LastRow, LastColumn).Range.End
  
  tblRange.Copy
  
  xlwb.Worksheets(1).Paste
  
  End With
  
  Set xlwb = Nothing
  Set xlApp = Nothing
  
  Set tblRange = Nothing
  Set tbl = Nothing
  Set doc = Nothing
  
  End Sub
  </code></pre>
  
  <p>However what I now have to do is applying this code to a certain folder with multiple doc(x) files. I would like to have the table of each separate Word file in a separate sheet within the same Excel file. How can I make <code>xlwb.Worksheets(1).Paste</code> dynamic?
  Also, would it be possible to first paste the file name of the Word file in the Excel sheet in the first cell and then copy the table next to it?</p>
  
  <p>Any guidance to incorporate these editions would be highly appreciated. </p>
  
  <p>ADDED: </p>
  
  <p>Using the advice below I have started to code the script in Excel:</p>
  
  <pre><code>Sub ImportWordTable()
  Dim wdDoc As Object
  Dim wdFileName As Variant
  Dim TableNo As Integer
  Dim iRow As Long
  Dim iCol As Integer
  
  filelist = Application.GetOpenFilename("Word files (*.doc),*.doc", , _
  "Browse for file containing table to be imported", MultiSelect:=True)
  
  If IsArray(filelist) Then
  
  For i = 1 To Len(filelist)
  wdFileName = filelist(i)
  Set wdDoc = GetObject(wdFileName)
  
  With wdDoc
  TableNo = wdDoc.Tables.Count
  If TableNo = 0 Then
  MsgBox "This document contains no tables", _
  vbExclamation, "Import Word Table"
  ElseIf TableNo &gt; 1 Then
  TableNo = InputBox("This Word document contains " &amp; TableNo &amp; " tables." &amp; vbCrLf &amp; _
  "Enter table number of table to import", "Import Word Table", "1")
  End If
  With .Tables(TableNo)
  For iRow = 1 To .Rows.Count
  For iCol = 1 To .Columns.Count
  'ActiveWorkbook.Sheets.Add After:=Worksheets(Worksheets.Count)
  'ActiveWorkbook.Sheets(Worksheets.Count).Name = Dir(wdFileName)
  'Worksheets(Dir(wdFileName)).Activate
  ActiveWorkbook.Sheets.Add(After:=Worksheets(Worksheets.Count)).Name = Dir(wdFileName)
  Worksheets(Dir(wdFileName)).Activate
  ActiveSheet.Cells(iRow, iCol) = WorksheetFunction.Clean(.Cell(iRow, iCol).Range.Text)
  Next iCol
  Next iRow
  End With
  End With
  wdDoc.Quit savechanges = False
  Next i
  Else
  wdFileName = filelist
  Set wdDoc = GetObject(wdFileName)
  With wdDoc
  TableNo = wdDoc.Tables.Count
  If TableNo = 0 Then
  MsgBox "This document contains no tables", _
  vbExclamation, "Import Word Table"
  ElseIf TableNo &gt; 1 Then
  TableNo = InputBox("This Word document contains " &amp; TableNo &amp; " tables." &amp; vbCrLf &amp; _
  "Enter table number of table to import", "Import Word Table", "1")
  End If
  With .Tables(TableNo)
  For iRow = 1 To .Rows.Count
  For iCol = 1 To .Columns.Count
  Cells(iRow, iCol) = WorksheetFunction.Clean(.Cell(iRow, iCol).Range.Text)
  Next iCol
  Next iRow
  End With
  End With
  End If
  
  
  Set wdDoc = Nothing
  
  End Sub
  </code></pre>
  
  <p>I can now select multiple files and also have added a function which names the sheets with the filename. However the code does not run properly after copying the info from the first file. It seems like the for loop does not update properly as I get a message: "this sheet name already exists". Maybe I'm missing some VBA logic here with regards to loops and indexing. </p>