Microsoft Office Forums

Go Back   Microsoft Office Forums > Microsoft Word > Mail Merge

Reply
 
LinkBack Thread Tools Display Modes
  #1  
Old 01-10-2018, 02:40 PM
Townshend Townshend is offline Windows 10 Office 2016
Novice
 
Join Date: Jan 2018
Posts: 4
Townshend is on a distinguished road
Default Mail Merge: Using One Excel File with Multiple Sheets

Good day! I've followed the thread on combining multiple values into a single letter using mail merge here which I seem to follow: http://www.msofficeforums.com/mail-m...html#post23345



Where I am getting stuck is utilizing an Excel file with two spreadsheets as sources of information.

The goal is to do a mail merge with one letter per ID, that references Name, Address, City, State, and ZipCode found on the Summary sheet along with information form the Detail sheet in the letter. Is this possible and if so, how? Also open to 3rd party tools that may assist.

Thanks!
Attached Files
File Type: xlsx test.xlsx (11.5 KB, 45 views)
Reply With Quote
  #2  
Old 01-15-2018, 03:46 PM
macropod's Avatar
macropod macropod is online now Windows 7 64bit Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 19,098
macropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to behold
Default

The approach in the link you referred to won't work for this kind of setup. Instead, you need to employ a DATABASE field in a normal ‘letter’ mailmerge main document. For a description of that, see:
https://answers.microsoft.com/en-us/...f-8642e46fa103
__________________
Cheers,
Paul Edstein
[MS MVP - Word]
Reply With Quote
  #3  
Old 01-15-2018, 03:47 PM
Townshend Townshend is offline Windows 10 Office 2016
Novice
 
Join Date: Jan 2018
Posts: 4
Townshend is on a distinguished road
Default

Thanks! I'll give this a shot and follow up if I have any questions
Reply With Quote
  #4  
Old 01-15-2018, 05:23 PM
macropod's Avatar
macropod macropod is online now Windows 7 64bit Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 19,098
macropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to behold
Default

I've had a bit of a play with your data. Try the attached - it assumes the mailmerge main document is saved in the same folder as your workbook and the workbook name is 'DB Merge Data.xlsx' (change the name in the DATABASE field to suit).
Attached Files
File Type: docx Incident Merge.docx (28.1 KB, 78 views)
__________________
Cheers,
Paul Edstein
[MS MVP - Word]
Reply With Quote
  #5  
Old 01-17-2018, 01:29 PM
Townshend Townshend is offline Windows 10 Office 2016
Novice
 
Join Date: Jan 2018
Posts: 4
Townshend is on a distinguished road
Default

This looks great, thank you!

Two follow up questions: is it possible to get the total of the amounts in the rows? also when I go to insert date { DATE \@ "MMMM d, yyyy" } then do Mailings > Finish & Merge > Edit Individual Documents it shows the code at the top instead of the actual Date. Strangely if I do CREATEDDATE that works.

Thanks!
Reply With Quote
  #6  
Old 01-17-2018, 03:25 PM
macropod's Avatar
macropod macropod is online now Windows 7 64bit Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 19,098
macropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to behold
Default

Quote:
Originally Posted by Townshend View Post
is it possible to get the total of the amounts in the rows?
Possibly, but I'm not that fluent in SQL.
Quote:
Originally Posted by Townshend View Post
when I go to insert date { DATE \@ "MMMM d, yyyy" } then do Mailings > Finish & Merge > Edit Individual Documents it shows the code at the top instead of the actual Date. Strangely if I do CREATEDDATE that works.
That suggests you're typing the braces instead of inserting them via Ctrl-F9. Tow things to note:
1. A plain DATE field will update every time you open the mailmerge output document. The Mailmerge Tips & Tricks 'sticky' thread at the top of this forum has an entry showing how to overcome that.
2. A CREATEDATE field returns the date the mailmerge main document was created, not the date the output document is created (unless you then do a SaveAs and refresh the field display).
__________________
Cheers,
Paul Edstein
[MS MVP - Word]
Reply With Quote
  #7  
Old 01-17-2018, 03:40 PM
Townshend Townshend is offline Windows 10 Office 2016
Novice
 
Join Date: Jan 2018
Posts: 4
Townshend is on a distinguished road
Default

Quote:
Originally Posted by macropod View Post
Possibly, but I'm not that fluent in SQL.

That suggests you're typing the braces instead of inserting them via Ctrl-F9. Tow things to note:
1. A plain DATE field will update every time you open the mailmerge output document. The Mailmerge Tips & Tricks 'sticky' thread at the top of this forum has an entry showing how to overcome that.
2. A CREATEDATE field returns the date the mailmerge main document was created, not the date the output document is created (unless you then do a SaveAs and refresh the field display).
QUOTE did the trick, thanks!
Reply With Quote
  #8  
Old 01-24-2019, 08:25 PM
mdlewis mdlewis is offline Windows 7 64bit Office 2010
Novice
 
Join Date: Jan 2019
Posts: 4
mdlewis is on a distinguished road
Default

I'm new and have sent a longer version to the wrong group. But my problem is the same and I get the same problem with this example. When I move from one merge record to the next, the displayed table does not update unless I rest the cursor on it and click "Update Field". Am I missing a Word/Merge setting?
Reply With Quote
  #9  
Old 01-24-2019, 08:32 PM
macropod's Avatar
macropod macropod is online now Windows 7 64bit Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 19,098
macropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to behold
Default

You're evidently only previewing the merge instead of completing it via Finish & Merge.
__________________
Cheers,
Paul Edstein
[MS MVP - Word]
Reply With Quote
  #10  
Old 01-24-2019, 09:15 PM
mdlewis mdlewis is offline Windows 7 64bit Office 2010
Novice
 
Join Date: Jan 2019
Posts: 4
mdlewis is on a distinguished road
Default Thanks - Works if you finish. BUT without finishing?

Thanks for your very fast reply. However, it does not solve my problem. I want each record - including table, editable before finishing. I can see that I will have to generate the table as a field in my source data. Just a BIT of VBA pre-processing. The original table approach with DATAFILE would have been read only, if it had worked.

But there is a curly one, why does the manual Update Field command update the current record's table values and is there any VBA which really detects change of record, enabling a VBA, update of the table - for that record?

If I add the standard "before or after mailmerge routine to the VBA, it does NOT fire up when there is a change of record BUT does - for every record in the merge set, when I do the manual Update field for the table/database instance.

I still think that I will need to create the table value(s) into the original merge dataset.

Thanks, Michael
Reply With Quote
  #11  
Old 01-24-2019, 09:18 PM
macropod's Avatar
macropod macropod is online now Windows 7 64bit Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 19,098
macropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to behold
Default

Quote:
Originally Posted by mdlewis View Post
Thanks for your very fast reply. However, it does not solve my problem. I want each record - including table, editable before finishing.
That simply isn't possible - neither mailmerges in general nor DATABASE fields in particular - permit that. What's the objection to doing the edits post-merge?
__________________
Cheers,
Paul Edstein
[MS MVP - Word]
Reply With Quote
  #12  
Old 01-24-2019, 09:41 PM
mdlewis mdlewis is offline Windows 7 64bit Office 2010
Novice
 
Join Date: Jan 2019
Posts: 4
mdlewis is on a distinguished road
Default Editable / Updatable Database fields.

Thanks "macropod".

I understand - a typical record set would be less than 10 - in the merge set.

The process would be

Start merge
Change non-table/database fields and layout for particular exercise.
Finish merge into separate docs and modify (no longer linked) table info on each as required.

Will work - but if the non-table fields needed further change - the whole modify table approach would be overwritten. The "further change" scenario is very likely.

As Malcolm 1 quoted, "Life wasn't meant to be easy"

Michael
Reply With Quote
  #13  
Old 01-24-2019, 10:05 PM
macropod's Avatar
macropod macropod is online now Windows 7 64bit Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 19,098
macropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to behold
Default

If your mailmerge main document is properly formatted and you use the appropriate query parameters and field coding, there shouldn't be any post-merge editing to do.

I have no idea what you mean by:
Quote:
Originally Posted by mdlewis View Post
if the non-table fields needed further change - the whole modify table approach would be overwritten
__________________
Cheers,
Paul Edstein
[MS MVP - Word]
Reply With Quote
  #14  
Old 01-24-2019, 10:33 PM
mdlewis mdlewis is offline Windows 7 64bit Office 2010
Novice
 
Join Date: Jan 2019
Posts: 4
mdlewis is on a distinguished road
Default

I have a client who generate the info from another package into a txt file which I will translate into relevant related tables. The master table is the merge data and I had hoped to link the related data into tables/database merge fields.

The client wants a document whose fields and layout may be added to, changed etc for any particular run - and then possibly changed again - several sources of massaging and data display. So the exercise may be repeated as if it were a single level document .

The subsidiary table info would be then be resourced afresh for each "re-run" so any already made change to generated documents, would be overwitten - same names.

I've thought of another way to give ultimate flexibility, if a subsidiary, non merge doc can be linked into a merge doc so that each main merge record can find the doc.

For a given set of records.

1. Place the table/database field into a separate - single level - merge doc based on the main merge dataset.
2. Run it and generate the doc for each instance into a carefully named ordinary word doc.

3. Run the main merge doc and dynamically add in a subsidiary (non merge) doc as generated above. (1:1) All fields would be changeable - the merge fields and layout and the local fields in the attached doc.

This depends on word merge being able to "link", "add in" a subsidiary carefully named doc.

As the number of records in the main set will not be very large, this may be possible using an extended If statement. The records in the set will always have a field 1,2,3,4, etc which appears as the start of the name of the parallel generated non-merge doc,. although a variably named "link" "add in" would be preferable.
Reply With Quote
  #15  
Old 01-24-2019, 10:37 PM
macropod's Avatar
macropod macropod is online now Windows 7 64bit Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 19,098
macropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to behold
Default

In that case - if you're not willing to do your editing post merge - instead of trying to coerce a mailmerge & DATABASE fields into doing what you want, perhaps you should consider hiring a programmer to developing something better suited to your needs.
__________________
Cheers,
Paul Edstein
[MS MVP - Word]
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Merge one Excel file into multiple Word Documents Rushpac Mail Merge 2 01-27-2017 02:49 PM
Insert slip sheets during a mail merge mcboats2 Mail Merge 1 12-07-2015 05:01 PM
Mail merge from excel - need to create sheets and create a table bluenosebex Mail Merge 5 08-02-2015 05:34 PM
Code for mail merge to reference saved excel file jtemp57 Word VBA 10 12-16-2013 11:02 PM
How do I merge data from one sheet in a workbook out into multiple sheets nolesca Excel 4 06-07-2010 08:13 AM


All times are GMT -7. The time now is 04:02 PM.


Powered by vBulletin® Version 3.8.1
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
SEO by vBSEO ©2011, Crawlability, Inc.
MSOfficeForums.com is not affiliated with Microsoft