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:

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...

Answer 1

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

Answer 2

You can use Word's Catalogue/Directory Mailmerge facility for this (the terminology depends on the Word version). To see how to do so with any mailmerge data source supported by Word, check out my Microsoft Word Catalogue/Directory Mailmerge Tutorial at:

http://www.msofficeforums.com/mail-merge/38721-microsoft-word-catalogue-directory-mailmerge-tutorial.html

or:

http://www.gmayor.com/Zips/Catalogue%20Mailmerge.zip

The tutorial covers everything from list creation to the insertion & calculation of values in multi-record tables in letters. Do read the tutorial before trying to use the mailmerge document included with it.

Depending on what you're trying to achieve, the field coding for this can be complex. However, since the tutorial document includes working field codes for all of its examples, most of the hard work has already been done for you - you should be able to do little more than copy/paste the relevant field codes into your own mailmerge main document, substitute/insert your own field names and adjust the formatting to get the results you desire. For some worked examples, see the attachments to the posts at:

http://www.msofficeforums.com/mail-merge/9180-mail-merge-duplicate-names-but-different-dollar.html#post23345

http://www.msofficeforums.com/mail-merge/11436-access-word-creating-list-multiple-records.html#post30327

Another option would be to use a DATABASE field in a normal ‘letter’ mailmerge main document and a macro to drive the process. An outline of this approach can be found at:

http://answers.microsoft.com/en-us/office/forum/office_2010-word/many-to-one-email-merge-using-tables/8bce1798-fbe8-41f9-a121-1996c14dca5d

Conversely, if you're using a relational database or, Excel workbook with a separate table with just a single instance of each of the grouping criteria, a DATABASE field in a normal ‘letter’ mailmerge main document could be used without the need for a macro. An outline of this approach can be found at:

https://answers.microsoft.com/en-us/msoffice/forum/msoffice_word-mso_winother-mso_2010/mail-merge-to-a-word-table-on-a-single-page/4edb4654-27e0-47d2-bd5f-8642e46fa103

For a working example, see:

http://www.msofficeforums.com/mail-merge/37844-mail-merge-using-one-excel-file-multiple.html

Alternatively, you may want to try one of the Many-to-One Mail Merge add-ins, from:

Graham Mayor at http://www.gmayor.com/ManyToOne.htm; or

Doug Robbins at https://onedrive.live.com/?cid=5AEDCB43615E886B&id=5AEDCB43615E886B!566