title: Automatically add rows into a MS Word table in a loop as long as you read data from the excel sheet
body: <p>I'm a VBA newbie and having lots of errors and problems as I go on about this task. Any help will be appreciated.</p>
  
  <p>Basically I have a textbox (TextBox1) in a Word doc. that I have for users to type in. It'll be a number and that text references back to a folder with Excel files with numbers as their name. </p>
  
  <p>All the Excel files in that folder has the same formatting (created from the same template), but the number of rows in each file differs from one another.</p>
  
  <p>My boss wants me to create a VBA code in Word to import data from Excel into the word doc, but the problem occurs with varying number of rows in each file, as users can select from any existing file in the folder.</p>
  
  <p>Someone suggested creating rows in a loop as long as there is data to pull from Excel, but I just cannot get the code to work. It especially keeps failing where I'm setting the word table cell and Excel cell equal to each other (which I don't even know I'm doing right).</p>
  
  <p>Here is what I have so far:</p>
  
  <pre><code>Private Sub CommandButton1_Click()
  Dim tbl As Table
  Dim row As row
  
  Set tbl = ActiveDocument.Tables(3)
  Set row = tbl.Rows.Add(BeforeRow:=tbl.Rows(1))
      tbl.Rows(1).Range.FormattedText = tbl.Rows(2).Range.FormattedText
      '~~~&gt; This is required as the above code inserts a blank row in between
      tbl.Rows(2).Delete
  
  Dim objExcel As Excel.Application
  Dim exWb As Excel.Workbook
  On Error Resume Next
  Set exWb = objExcel.Workbooks.Open("S:\Electro-Protocol\Mot_Protocols\" &amp; TextBox1 &amp; ".xls")
  
  row.Cells(0) = exwb.Sheets("Tabelle1“).Cells(4,1)
  'Now you just need to define the loop in which you execute the previous mentioned code. Here's some pseudo code you got to adapt in VBA for yourself:
  
  For counter = 0 To ExcelSheetLength Step 1
  
         row.Cells(1) = exWb.Sheets("Tabelle1").Cells(counter, 0)
         row.Cells(2) = exWb.Sheets("Tabelle1").Cells(counter, 1)
         row.Cells(3) = exWb.Sheets("Tabelle1").Cells(counter, 2)
   Next counter
  
  For counter = 0 To ExcelSheetLength Step 1
  If counter = 25 Or counter = 26 Or counter = 27 Then
      'nothing
   Else
      Set row = tbl.Rows.Add(BeforeRow:=tbl.Rows(2))
  
  End If
   Next counter
  End Sub
  </code></pre>
  
  <p>So basically, it's a mess. I'm trying to import data from a selection of Excel worksheets with the same format, but varying number of rows into a Word table that'll dynamically add rows in a loop as long as I have data to read in the Excel sheet.</p>
  
  <p>The information I want from Excel starts at A4 and goes to D4, which I want in Word doc Tables(3), starting at row 1, column 1 to column 4. The Excel template repeats itself over and over until it is finished with all the data. There is 5 repeated unnecessary rows after every 8 rows. So not sure how to loop that around since I'm planning on ignoring/skipping the 5 repeating rows of unnecessary info while importing data into Word.</p>
  
  <p>I know this sounds really confusing, so let me know if you have any questions.
  Thanks in advance!</p>