Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Closed Thread
 
Thread Tools Display Modes
  #1  
Old 01-10-2018, 02:40 PM
Townshend Townshend is offline Mail Merge: Using One Excel File with Multiple Sheets Windows 10 Mail Merge: Using One Excel File with Multiple Sheets Office 2016
Novice
Mail Merge: Using One Excel File with Multiple Sheets
 
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: https://www.msofficeforums.com/mail-...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, 221 views)
  #2  
Old 01-15-2018, 03:46 PM
macropod's Avatar
macropod macropod is offline Mail Merge: Using One Excel File with Multiple Sheets Windows 7 64bit Mail Merge: Using One Excel File with Multiple Sheets Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
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
[Fmr MS MVP - Word]
  #3  
Old 01-15-2018, 03:47 PM
Townshend Townshend is offline Mail Merge: Using One Excel File with Multiple Sheets Windows 10 Mail Merge: Using One Excel File with Multiple Sheets Office 2016
Novice
Mail Merge: Using One Excel File with Multiple Sheets
 
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
  #4  
Old 01-15-2018, 05:23 PM
macropod's Avatar
macropod macropod is offline Mail Merge: Using One Excel File with Multiple Sheets Windows 7 64bit Mail Merge: Using One Excel File with Multiple Sheets Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
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. The DATABASE field's code for this is:

{DATABASE \d "{FILENAME \p}/../DB Merge Data.xlsx" \s " SELECT FORMAT([Date Of Incident]&' '&[Time], 'DD-MMM-YYYY @ hh:mm') AS `Incident Date & Time`, [Location], Format([Amount],'$#,##0.00') AS Amount, Format([Charge],'$#,##0.00') AS Charge, Format([Service Fee],'$#,##0.00') AS `Service Fee`, [Invoice Date] FROM [Detail$] WHERE [ID] = {MERGEFIELD ID} ORDER BY [Invoice Date] " \l "15" \b "49" \h}

The field coding assumes the workbook name is 'DB Merge Data.xlsx' and that the worksheet name is 'Detail'. Change the names in the field to suit.
Attached Files
File Type: docx Incident Merge.docx (28.1 KB, 309 views)
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
  #5  
Old 01-17-2018, 01:29 PM
Townshend Townshend is offline Mail Merge: Using One Excel File with Multiple Sheets Windows 10 Mail Merge: Using One Excel File with Multiple Sheets Office 2016
Novice
Mail Merge: Using One Excel File with Multiple Sheets
 
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!
  #6  
Old 01-17-2018, 03:25 PM
macropod's Avatar
macropod macropod is offline Mail Merge: Using One Excel File with Multiple Sheets Windows 7 64bit Mail Merge: Using One Excel File with Multiple Sheets Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

Quote:
Originally Posted by Townshend View Post
is it possible to get the total of the amounts in the rows?
No.
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. Two 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
[Fmr MS MVP - Word]

Last edited by Charles Kenyon; 04-06-2019 at 06:23 AM. Reason: typo fix
  #7  
Old 01-17-2018, 03:40 PM
Townshend Townshend is offline Mail Merge: Using One Excel File with Multiple Sheets Windows 10 Mail Merge: Using One Excel File with Multiple Sheets Office 2016
Novice
Mail Merge: Using One Excel File with Multiple Sheets
 
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!
  #8  
Old 06-06-2019, 05:19 AM
Sarki76 Sarki76 is offline Mail Merge: Using One Excel File with Multiple Sheets Windows 10 Mail Merge: Using One Excel File with Multiple Sheets Office 2019
Novice
 
Join Date: Jun 2019
Posts: 7
Sarki76 is on a distinguished road
Default

Dears,
I have just tried how this mail merge with filtering work, but this does not seem to be working for me. I have tried several examples listed on this site, even the above one - but when doc opened the table data (with database field) does not change according to base data eg for Homer Simpson I see the same table (as per example incident reports and costs) as for Bart Simpson... noi change in data...
Am I doing something wrong or my approach is different to given example?
Thx, Olivér
  #9  
Old 06-06-2019, 06:26 AM
macropod's Avatar
macropod macropod is offline Mail Merge: Using One Excel File with Multiple Sheets Windows 7 64bit Mail Merge: Using One Excel File with Multiple Sheets Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

Quote:
Originally Posted by Sarki76 View Post
Am I doing something wrong or my approach is different to given example?
Probably.

Without actually seeing the problem mailmerge main document and a sample data source, it can be difficult for anyone to diagnose the issue. Can you attach them to a post with some representative data (delete anything sensitive)? You do this via the paperclip symbol on the 'Go Advanced' tab at the bottom of this screen.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
  #10  
Old 06-06-2019, 07:12 AM
Sarki76 Sarki76 is offline Mail Merge: Using One Excel File with Multiple Sheets Windows 10 Mail Merge: Using One Excel File with Multiple Sheets Office 2019
Novice
 
Join Date: Jun 2019
Posts: 7
Sarki76 is on a distinguished road
Default

Hi, I'm using exactly the same files that are attached to this thread:
Incident merge.docx and test.xlsx
what I see is the below tablke for everyone from summary tab

Incident Date & Time
Location
Amount
Charge
Service Fee
Invoice Date
08-febr-2017 @ 17:23
Candy Lane
$2,22
$12,63
$21,00
04/01/2017
08-febr-2017 @ 17:09
Royal Lane
$0,63
$12,63
$21,00
04/01/2017

Last edited by Sarki76; 06-06-2019 at 07:14 AM. Reason: no pictures were uploaded, needed to add the content manually
  #11  
Old 06-06-2019, 04:02 PM
macropod's Avatar
macropod macropod is offline Mail Merge: Using One Excel File with Multiple Sheets Windows 7 64bit Mail Merge: Using One Excel File with Multiple Sheets Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

That indicates you've connected your mailmerge main document to the wrong worksheet. It should connect to the 'Summary' sheet, not the 'Detail' sheet.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
  #12  
Old 06-07-2019, 01:43 AM
Sarki76 Sarki76 is offline Mail Merge: Using One Excel File with Multiple Sheets Windows 10 Mail Merge: Using One Excel File with Multiple Sheets Office 2019
Novice
 
Join Date: Jun 2019
Posts: 7
Sarki76 is on a distinguished road
Default

OK, I got that working by separating the exfel file containing multiple sheets into several individual files.
BUT: how am I supposed to get the external data refreshed automatically when I change the record in the main doc?
is there a way to get it auto-updated?
Thx in advance!
  #13  
Old 06-07-2019, 04:35 AM
macropod's Avatar
macropod macropod is offline Mail Merge: Using One Excel File with Multiple Sheets Windows 7 64bit Mail Merge: Using One Excel File with Multiple Sheets Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

Quote:
Originally Posted by Sarki76 View Post
OK, I got that working by separating the exfel file containing multiple sheets into several individual files.
Why did you do that? It's quite unnecessary.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
  #14  
Old 06-22-2021, 03:31 PM
QNRyfzKPYQyC QNRyfzKPYQyC is offline Mail Merge: Using One Excel File with Multiple Sheets Mac OS X Mail Merge: Using One Excel File with Multiple Sheets Office 2016 for Mac
Novice
 
Join Date: Jun 2021
Posts: 4
QNRyfzKPYQyC is on a distinguished road
Default did Office change? I get connection errors

Hi, have been trying to make this work on my mac but unfortunately without success. I've managed to connect to the file, but the sql-string seems te generate an error ("can't open data file" translated from dutch). I've simpified the sql from the tutorial above into

\s " SELECT [Subordinate Forename] FROM [Sheet1$] WHERE [Manager ID] = { MERGEFIELD Manager_ID } "

Still no luck. Please help.
test.docx

test.xlsx
  #15  
Old 06-22-2021, 03:35 PM
macropod's Avatar
macropod macropod is offline Mail Merge: Using One Excel File with Multiple Sheets Windows 10 Mail Merge: Using One Excel File with Multiple Sheets Office 2016
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

I don't see anything in the 'code' you posted to identify the data source, which is what the string:
\d "{FILENAME \p}/../DB Merge Data.xlsx"
does.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Closed Thread

Thread Tools
Display Modes


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

Other Forums: Access Forums

All times are GMT -7. The time now is 10:35 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft