VBA Table in Merged Letter

Question

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:

How can I insert this data into a newly created table after the placeholder "insert_table_here"?
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...

Zack · Accepted Answer

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

VBA Table in Merged Letter

Answers (2)

Related Questions