vbaexcelms-wordmailmergedocx-mailmerge

VBA Table in Merged Letter


I have an Excel Sheet with a lot of customer Data. All customers have common data (address, name etc.) that I implemented as simple mergefields. Some Customers have multiple Datasets that should be added as a Table at the end of the merged letter. To find the Data from my excel Sheet I already came up with the following code. noInt is the number of customers while noData is the number of different datasets (all customers together, some multiples). exWb is the excel workbook my data comes from and the data I want to display in the table lays in columns 5 to 9.

For i = 2 To noInt
    
    For k = 2 To noData
        
        If exWb.Sheets("Table1").Cells(k, 1) = exWb.Sheets("Table2").Cells(i, 1) Then
            For j = 5 To 9
            

Insert into Table exWb.Sheets("Table1").Cells(k, j)

            Next j
        End If
    Next k
Next i

Now my questions:

  1. How can I insert this data into a newly created table after the placeholder "insert_table_here"?

  2. How can I make sure that for every letterin the mail merge series there is only the data of the customer the letter is about included in this table?

    To find a solution to this, I already thought about if there was maybe a function that gives the current "Mail Merge Number". In that case I could compare the field (MailMergeNumber, 1) with (k,1) to only show the results that include the current customer.

Example to make it more understandable:

Dear Mr A,

...

Table of items Mr. A bought

-End of document-

Dear Mr. B,

...

Table of items Mr. B bought

-End of document-

And so on...


Solution

  • If you're creating Word documents from a template (that's generally the easiest way I've found to do it), you can add a table to the template document with the header rows you need, and 1 blank row for the data. Then, after populating the basic mergefields, you could loop through the current customer fields, adding new rows to the Word table as you went. Something like this:

    Dim exWs as Excel.Worksheet
    Dim CurrentCustomerFirstCell as Excel.Range
    Dim CurrentCustomerActiveCell as Excel.Range
    Dim EmpRowOffset as integer
    Dim wdDoc as Word.Document
    Dim wdTable as Word.Table, wdCell as Word.Cell
    
    ' set up your existing references, including (I assume) to the Word document you're updating
    
    set exWs = exWb.Sheets("Table1")
    
    ' initialize row for current employee
    CurrentCustomerFirstCell = exWs.Cells(2,1)
    
    do while CurrentCustomerFirstCell.Row <= noData ' consider renaming noData to somthing like "numberOfRows"
        ' populate basic mergefields
        wdDoc.Fields(1).Result.Text = CurrentCustomerFirstCell.Value
        ' etc.
    
        ' populate table in Word document
        set wdTable = wdDoc.Tables(1)
    
        EmpRowOffset = 0
        set CurrentCustomerActiveCell = CurrentCustomerFirstCell.Offset(Rowoffset:=EmpRowOffset)
    
        set wdTable = wdDoc.Tables(1)
    
        do while CurrentCustomerActiveCell.Value = CurrentCustomerFirstCell.Value
            ' this code would update the first "data" row in the existing Word table
            ' to the 6th column of the active employee row
            set wdCell = wdTable.Cell(Row:=2 + EmpRowOffset, Column:=1)
            wdCell.Range.Text = _
                    CurrentCustomerActiveCell.Offset(columnoffset:=5).Value
            wdTable.Rows.Add
    
            EmpRowOffset = EmpRowOffset + 1
            set CurrentCustomerActiveCell = CurrentCustomerFirstCell.Offset(RowOffset:=EmpRowOffset)
        Loop
    
        ' now that we're finished processing the employee, update CurrentCustomerFirstCell
        set CurrentCustomerFirstCell = CurrentCustomerActiveCell
    loop