Inserting Formatted Table Via Mail Merge

In my view, to produce this report in Merge, you would probably still be better off "flattening" all the data and using one of the methods that has already been suggested.

However, if you really need not to flatten tthe data to that extent, I have posted an example based on a different approach which inserts the rate history data using a DATABASE field, separating the data into two worksheets.

I hope you will be able to download the example from here: http://goo.gl/6Dkkxx

To see what it is doing, you will need to download the .zip (let me know if you cannot do that) and unzip the .docm and .xlsx files into a folder called c:\insrates. I have used a .docm because this approach will require you to run a small macro to format the rate history table post-merge. If you are not allowed to run macros, this approach is unlikely to work for you.

Then I suggest you open the .xlsx file, where you will see two sheets:

main, containing basic, current details like this

State    Form    Rate 1     Rate 2....  

IN         M3        $2,324    $2,541    

and detail, containing the rate history (actually more like a rate change history), that repeats the two key fields State and Form and has columns for Year, Rate (change), and Reason. There's a special row at the top of the rate table to deal with some problems Word has getting data from Excel. Don't delete it!

The .docm is a Mail Merge Main document, but it needs to be hooked up to its data source. Once done, you shouldn't need to do that again unless you move the files.

 To hook it up, you need to do two things. Leave the .xlsx open for the time being and open the .docm. ensure that field codes are displaying (so that you can see { MERGEFIELD Form }, for example). Don't attempt to update any fields at this point.

Then look through the document until you find a field that starts like this:

{ XDATABASE

Delete the "X", and if you have changed the location of the .xlsx, modify the file name to match. Backslashes need to be doubled up - e.g. c:\a\myfile.xlsx needs to be specified as c:\a\myfile.xlsx. and \\myserver\myshare\myfile.xlsx needs to be specified as \\myserver\myshare\myfile.xlsx

Notice that the DATABASE field has some SQL with a WHERE clause that contains some { MERGEFIELD } fields. This is so that you grab the history records associated with the State/Form pair currently being merged. Since we haven't attached the mailmerge data source yet, trying to update the DATABASE field's results should fail. You may also see that I have had to insert a bit of SQL to deal with the fact that you can have either percentages or text in your "Rate" column.

Now use Mailings->Select Recipients to locate the .xlsx as the data source for the merge. In the list of tables select the "main$" table.

If that works OK, I suggest you do a Save As to save the .docm immediately.

Then show the field code results via Alt-F9, and try previewing the merge. You should see the DATABASE table reult change as you change record.

If you now merge to an output document, the DATABASE fields should be replaced by the appropriate rate table. However, Word will use default column widths, and they almost certainly won't be suitable. You can try to fix this problem by modifying the DATABASE field so that it has a \*MERGEFORMAT switch at the end, but I do not believe that actually solves the problem, and the format is very easy to lose if you modify anything.

So I have provided a bit of VBA that goes through the output document and reformats the columns. It also right-justifies the rate column. At the moment, this macro is stored in the ThisDocument module of the .docm - I leave you to work out how to run it.

Assuming you have managed to follow all that, it really just leads me to some more questions.

1. The main question is about the fact that you have a "formatted" table. Here, I have assumed that what you are really trying to do is avoid flattening the data "too much" and impose a consistent format, year-on-year, and that is what the formatting macro would attempt to do. But if for example you are marking up the resulting table (perhaps with some footnotes or comments), then this won't work. In fact, if you are doing that, I suspect that the only way you will achieve what you want is via a workflow where you do something like the following:

 a. ensure that the table layout is fixed (i.e. all column widths are fixed

 b. generate an initial merge document for each state/form combination

 c. use a macro to do a separate merge for each state/form

 d. next year, use a macro to copy the table generated the previous year into the mail merge main document for the state/form combination, ensuring that you have one row at the end of the table with the merge fields you need for the new year.

 e. repeat (c).

However, I suspect it would be simpler not to do a merge in that case. You would probably be better off having a macro that looked up the correct data for each state/form combination and inserted it.

2. It wasn't completely clear to me whether you were producing one document per state/form combination, or one document per company/state/form combination (because "company" is mentioned at the beginning of your mail merge main document but nowhere in your Excel data.
3. There are a few other things I've highlighted in my sample .docm.