Inserting Formatted Table Via Mail Merge

Hello everybody,

I'm not sure if this should go in the excel or word subforums since it involves both. I am trying to create a number of documents that are largely identical with specific values inserted in the correct spots. It seemed like a perfect situation for a mail merge. The issue is that each document has a semi-specific table that I need to add a line to. I've considered doing some sort of find and replace, but I don't think mail merge can take multiple existing documents as input. I'm under the impression that it's a one template-> multiple documents features, not n templates -> n documents.

This has led me to think that what I need is to have a cell in excel that contains the formatted table, a cell with the new line, and a cell that joins the two. Then each time, I would copy over the previous "final" table and change the added line to produce the new table. I could then insert the formatted table into the word document. The issue I'm having is that I can't get the mail merge to do this. I tried pasting the table into the cell and pasting the html for the table, but I can't figure out how to insert a formatted table into word via mail merge.

My table should look something like:

Year Percent Reason

1995 0% First year

1996 2% Increase required due to...

Thank you for any help on this,

k64

Note: Question also posted at http://answers.microsoft.com/en-us/o...0-cdecc8809b57

Attachments:
Merge Sample.xlsx
Sample.docx

The many-to one mailmerges available from:
Graham Mayor at http://www.gmayor.com/ManyToOne.htm; and
Doug Robbins at https://skydrive.live.com/?cid=5aedc...615E886B%21566

Are quite flexible and can easily handle variable numbers of rows per record. In addition to a 'Many to One' merge, the latter handles:
• Merge with Charts
• Duplex Merge
• Merge with FormFields
• Merge with Attachments
• Merge to Individual Documents
• Merge, Print and Staple

There is also no particular reason you can't conditionally output variable, formatted text, via IF fields that test the value of a given mergefield. The variable data don't all need to be in the data source.

Having seen your document, I'm not even sure what you're trying to do involves mailmerge at all; it looks more like a case for data linking. You can do that by selecting a given Excel cell, copying it, then going into Word and using Paste Special>Paste Link with whatever format you prefer. From then on, any changes to the Excel data will be reflected in the document.

I considered data linking. My issue is that I need there to be 50+ copies of the documents, one for each State and Form. If I did data linking, I think they would all be linked to the exact same excel data, instead of the data for the next state and form. I also need to keep old versions of the documents for records and make new versions each time we file. It seems like if I set up the documents to have data linking, I would have to make copies of them all, and manually update all the links.

If there is a way to do some sort of dynamic data linking, so that each document, the links will be shifted down one row, and they can all be switched to a new excel sheet when needed, then I'm interested, but I was under the impression that you need mail merge to do that.





The excel file looks roughly like this:

State Form Rate 1 Rate 2....

IN M3 $2,324 $2,541

IN M5 $2,100 $2,257

CA M2 .....

....


Everything in my documents works and merges correctly except for one section:


Rate History

Year Rate Reason

2012 4% [Explanation] <-These are unique to the state/form

2013 0% [Explanation] <-But all the previous year entries stay the same

-> A line needs to be added here for 2014 <-


I need to have a way to "Add a line" to the unique rate history table for each state/form. The solution I originally attempted was to add columns to the excel file

State Form Rate 1 Rate 2.... Rate History_____ Rate Reason____ New Rate Table

IN___ M3__ $2,324 $2,541 [entire Rate Table] 5% [Explanation] [Entire table w/ new line]

IN___ M5__ $2,100 $2,257 [entire Rate Table] 2% [Explanation] [Entire table w/ new line]

CA___ M2__ .....

....


So my first question was whether I could have an entire formatted table in one cell, using html or something else.

If that isn't possible or there is a better option, then my question is how to produce 50+ new documents with the same unique rate history as their past versions, but one line added and new data.


As I said, the rest of the mail merge works fine and all the values come in as desired. This is just difficult because the previous rate history isn't static, it differs for each record.


I hope this clarifies things,


Thank you again for your help

Perhaps, then, you could attach some sample data in an Excel workbook, so we can see what we're working with.

There seems to be some disconnect between your document and data. Your document refers to '2012 Premium Rates' but there's nothing apparently related to those in the workbook. And, where there are figures for 2013 & 2014, it's by no means apparent which of the four sets of rates for each year you want to use and which one go where. Then there's the schedule you have going back to 1988, for which there appears to be no data.

Also, FWIW, your Sheet2 is unnecessary for a mailmerge - any difference in formatting that you might require between the data and the document can be handled by field switches in the document.