Easiest way to mail merge multiple tables into Word 2016

I'm trying to use Mail Merge to insert product specification tables into user manuals. Each manual includes 3 separate tables with 3 separate sets of information, all on the same page (see below for an example). I don't have a lot of experience using Mail Merge, but I can't figure out how to do this. Any time I change Data Sources, it changes everything in each table, or exports three copies of the same document.

Is Mail Merge the best way to do this? I have tried Special Pasting Excel tables and linking back to them, which works well and automatically updates, but my supervisor needs the tables to be generated with just one click and populate from a large database.

Thanks in advance!

|

* Please try a lower page number.

* Please enter only numbers.

* Please try a lower page number.

* Please enter only numbers.

Do you need to use Mail Merge for any other reason on this project, or is it just so that you can include the data?

Where is the data actually stored? Is it in a "proper database" such as a SQL Server database?

Are you in a position to use VBA?

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

No, Mail Merge is just the only thing I can think of to automatically update and change multiple fields throughout a document. It was recommended to me by my supervisor. The product specs need to be easily changed and should automatically update in any manual that they are featured in. We have many.

I am creating the database in an Excel document, not an SQL Server. I tried my hand at MS Access, but I think it might be too advanced for me and what I am doing.

I have no knowledge of VBA.

 

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

My general view is that nothing you can do with either linking or mailmerge will be a "one click" solution that also looks good.

From an appearance point of view, the linking approach you have already tried is probably your best bet, but relying on autoupdate is probably not such a great idea - you have to expect that at the very least, people would have to 

 - select the body text of the document (ctrl-A) (I am assuming that none of these tables needs to be in any other place such as a header or footer).

 - update all the field codes (F9)

Two clicks doesn't seem all that bad to me and is arguably simpler than a mailmerge.

Another way you can bring in data from different places in Excel is to use { DATABASE } fields, which can insert tabular data, but unless the formatting styles that you can specify in such a field code are good enough, you would require VBA to apply the formatting you wanted. 

Just in case you want to explore, let's suppose your data is in a table in a sheet called Sheet1 in a workbook called c:\mywbs\mywb.xlsx

Then the following DATABASE field in Word will insert the table of data:

{ DATABASE \d "c:\\mywbs\\mywb.xlsx" \s "SELECT * FROM [Sheet1$]" }

(Use ctrl-F9 to insert the special field code brace pair {  } and type everything else in between. Then select and F9 to update, Alt-F9 to toggle between field code view and field results view.)


There are field code options ("switches") that let you specify a number of predefined formatting styles.

However, to achieve anything like the example you gave, you would need to do something like this:

Create a one-row, two cell table like this:

----------------------------------

| Name |                                  |

----------------------------------

Put the database field in cell 2. Except you can't because you can't put one directly inside a table. So instead you have to nest the DATABASE field like this:

{ SET mydb1 { DATABASE \d "c:\\mywbs\\mywb.xlsx" \s "SELECT * FROM [Sheet1$]" } }

(the outer pair of {  } also have to be inserted using ctrl-F9)

Then in cell 2, you put this

{ mydb1 }

Use ctrl-A, F9 to select and update everything. Then you will see how the formatting of the table is really not all that helpful. You might be able to improve it by first adding the field switch \* MERGEFORMAT at the end of the DATABASE field like this when it is not nested within the { SET } field

{ DATABASE \d "c:\\mywbs\\mywb.xlsx" \s "SELECT * FROM [Sheet1$]" \* MERGEFORMAT }

and then applying the formatting you need, but I suspect that it will not be a stable solution.

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

As Doug suggests, you may be able to achieve something close to what you want using DATABASE fields. For a practical demonstration, see:

http://www.msofficeforums.com/mail-merge/21847-mail-merge-into-different-coloumns.html#post67097

For your purposes, you might use a bookmarked FILLIN field to choose the 'name' and have the DATABASE fields reference that bookmark instead of being hard-coded as they are in the demo document. It would then only be a matter of refreshing the document's field display (e.g. via Ctrl-A, F9) to update the output.

Cheers
Paul Edstein
(Fmr MS MVP - Word)

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

I guess you meant "As Peter suggests".
Hope this helps,
Doug Robbins - MVP Office Apps & Services (Word)
dougrobbinsmvp@gmail.com
Screen shots by Snagit from www.techsmith.com

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

How is the data arranged in the data source?

Are each of the three tables in the same format?

If you send me a copy of the document and the data source, referencing this thread in the covering email message, I will take a look at them and see if I can suggest a way to do it.

Hope this helps,
Doug Robbins - MVP Office Apps & Services (Word)
dougrobbinsmvp@gmail.com
Screen shots by Snagit from www.techsmith.com

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

I guess you meant "As Peter suggests".

Oops, Sorry Peter...

Cheers
Paul Edstein
(Fmr MS MVP - Word)

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

It happens...:-)

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

Hi Paul,

Thanks for the advice! Do you have any details on how to use a bookmarked FILLIN field? I don't have much experience with using these so any threads, instructions, etc. would be helpful! This sounds like it would be very useful for what I'm trying to do.

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

Hi Peter,

I inserted all of the code into cell 2 of my table as you stated, but I got this error: Error! Bookmark not defined.

Does the { mydb1 } code need to go in Excel or Word?

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

* Please try a lower page number.

* Please enter only numbers.

* Please try a lower page number.

* Please enter only numbers.

 
 

Question Info


Last updated May 11, 2021 Views 3,850 Applies to: