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

test.xlsx (11.5 KB, 185 views)

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]

Thanks! I'll give this a shot and follow up if I have any questions

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

Incident Merge.docx (28.1 KB, 258 views)

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]

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!

No.
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]

QUOTE did the trick, thanks!

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

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]

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

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]

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!

Why did you do that? It's quite unnecessary.

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]

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

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]