Hi all first post from a no techie so please be gentle... Attached excel sheet and word document.

The excel sheet is in two halves, the green columns form the table at the top of the word doc. I want to pull into the word doc table any record that doesn't say NAD..



The second half of the excel sheet columns in purple is the rest of the info for the work sheets that follow on the word doc. Each record needs a sheet completed as per page 2 of the MM.

I can get the mail merge to create the sample sheets one for each record, but I cant get the table on page one completed. I need this table to have all the records except those that say NAD.

Can anyone help? Sorry if I'm being confusing or if there is a far more obvious/easy way of doing this!

Thanks

Attached Files

	MM data.xlsx (10.4 KB, 18 views)
	MM template.docx (21.2 KB, 17 views)

A mailmerge can't conditionally build a multi-row table this way. It can at best generate multiple one-row tables that you could consolidate post-merge. However, a DATABASE field can generate such a table. That said, it's not clear how this relates to the table on your second page, since a mailmerge typically generates one report per record but you seem to want all records on the first page.

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

Thank you. I need the tables on page 2 for all records so this works. The table in page 1 only includes some of the records, I.e all except those with the identification NAD. So yes I'll try the DATABASE field - where can I find out how to do this, googling create database field has not helped! Is there a idiots guide somewhere? Thank you

For an example of DATABASE field usage, see: https://www.msofficeforums.com/mail-...html#post67110
Be warned, this isn't for those who don't understand the intricacies of SQL. Besides which, you would also need to encompass the field in an IF test to ensure it only generates its output when the merge executes - not when each record is processed.

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

Hi several days later .... I've made a separate mail merge for the sample sheets which I copy and paste into my main report - works a treat...

I've also managed to make some code and pull the info through from the spreadsheet into the word doc tables (hurrah!) BUT... now if I make an update to the information in the excel sheet, the table wont update and I get an error message... This is the code I'm using below... Any help on what I'm dong wrong. Again sorry if this is obvious, but I've never even heard of SQL code before so am on a rather steep learning curve! Thanks

{DATABASE \d "W:\\XXXXX\\XXXXX\\Trial MM data RH.xlsx" \c "Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;Data Source=W:\\XXXX\\XXXXX\\Trial MM data RH.xlsx;Mode=Read;Extended Properties=\"HDR=YES;IMEX=1;\";Jet OLEDB:System database=\"\";Jet OLEDB:Registry Path=\"\";Jet OLEDB:Engine Type=37;Jet OLEDBatabase Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database Password=\"\";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDBon't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False;Jet OLEDB:Support Complex Data=False;Jet OLEDB:Bypass UserInfo Validation=False;Jet OLEDB:Limited DB Caching=False;Jet OLEDB:Bypass ChoiceField Validation=False" \s\"SELECT `Building`, `Floor`, `Room/Area` FROM `Sheet1$` WHERE ((`Extent of damage score` = '0')) ORDER BY `Sample`" \l "23" \b "191" \h}

If you're using Excel, you shouldn't need the connection string, as the link I pointed you to shows. Assuming the field works when first created, I can't see why it wouldn't also update when refreshed after the Excel data changes. Also, what is the error message? It's a bit hard to diagnose without the error message. That said, based on the workbook attached to your first post, you should be able to use something like:
or, if the document and data source are stored in the same folder:
where {FILENAME \p} is an embedded field, as per the link I gave you.

Note that it doesn't really matter whether you delineate the fields with `` or [].

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