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