A dynamic table

This example will generate a dynamic table, with one row added for each attached record (see below for an illustration of this).

In this example we add a single row table with three columns.

Table

The code for this example is:

Private Sub Document_Open() 
   ' 
   ' Setup the data source and run the merge 
   Set MainDocument = ActiveDocument 
   With ActiveDocument.MailMerge 
       .MainDocumentType = wdFormLetters 
       .OpenDataSource _ 
           Name:="", _ 
           connection:="DSN=EMu Loans;", _ 
           SQLStatement:="SELECT * FROM eloans.csv", _ 
           Subtype:=wdMergeSubTypeWord2000 
       .Destination = wdSendToNewDocument 
       .Execute Pause:=False 
   End With

 

   Dim key As String 
   Dim row As Integer 
   Dim table As Word.table 
   Dim connection As New ADODB.connection 
   Dim recordset As New ADODB.recordset 
   ' 
   ' Connect to the DSN that we are going to use 
   connection.Open "DSN=EMu Loans;" 
   ' 
   ' This code cycles through each table we have added to the Word document in order to populate 
   ' it with data. In this example we have only one table 
   For i = 1 To ActiveDocument.Tables.Count 
       With ActiveDocument.Tables(i) 
           ' 
           ' Copy and then modify the following "If" section of code (between the If and 
           ' End If statements) for each .csv file generated. 
           If InStr(.Cell(1, 1).Range.Text, "IRN") = 1 Then 
               ' 
               ' Identify the key - in this case it is located in row 1, column 2 
               key = .Cell(1, 2).Range.Text 
               key = Trim(Left(key, Len(key) - 2)) 
               ' 
               ' We want to display the IRN and Summary Data. 
               ' Because the number of attached Catalogue records is unknown, we use the 
               ' following code to add a row dynamically for each record. 
               row = 1 
               ' 
               ' You would replace the name of the .csv file and key as required 
               ' by your report. 
               ' In this example the .csv file is called ObjObjec.csv 
               ' and the key is eloans_key 
               recordset.Open "select * from ObjObjec.csv where eloans_key = " & key, connection 
               While Not recordset.EOF 
                   row = row + 1 
                   .Rows.Add 
                   .Cell(row, 1).Range.Text = recordset("irn") 
                   .Cell(row, 3).Range.Text = recordset("SummaryData") 
                   recordset.MoveNext 
               Wend 
               recordset.Close 
               ' 
               ' Remove the column containing the key so that it doesn't display 
               ' in the mail merge report 
               .Columns(2).Select 
               Selection.Columns.Delete 
   
            ' Remove the table borders 
            .Borders.Enable = False 
   
           End If 
       End With 
   Next i 
   ' 
   ' Close down our connections 
   connection.Close 
   ' 
   ' Force all fields to be updated in case we have images 
   ActiveDocument.Fields.Update 
   ' MainDocument.Close SaveChanges:=False 
End Sub 
Private Sub Document_Close() 
   '  When the document is closed we need to disconnect the 
   '  data source otherwise Word will try and use it 
   '  when opening the document 
   ActiveDocument.MailMerge.MainDocumentType = wdNotAMergeDocument 
   ActiveDocument.Save 
End Sub

The Visual Basic Editor will appear similar to this:

Code for dynamic table

  1. Save and close the main report document.
  2. At Step 1. Write the VB code we modified the VB code for testing purposes by commenting out MainDocument.Close SaveChanges:=False. Before we finalize the report we need to edit the VB code to remove the apostrophe.

    When the report is run, the following mail merge report will be generated, with a row added to the Objects table for each linked object:

    Mail merge report