Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 07-28-2015, 03:11 PM
bluenosebex bluenosebex is offline Mail merge from excel - need to create sheets and create a table Windows 8 Mail merge from excel - need to create sheets and create a table Office 2013
Novice
Mail merge from excel - need to create sheets and create a table
 
Join Date: Jul 2015
Posts: 3
bluenosebex is on a distinguished road
Default Mail merge from excel - need to create sheets and create a table


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
File Type: xlsx MM data.xlsx (10.4 KB, 8 views)
File Type: docx MM template.docx (21.2 KB, 7 views)
Reply With Quote
  #2  
Old 07-28-2015, 08:53 PM
macropod's Avatar
macropod macropod is offline Mail merge from excel - need to create sheets and create a table Windows 7 64bit Mail merge from excel - need to create sheets and create a table Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,224
macropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant future
Default

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]
Reply With Quote
  #3  
Old 07-29-2015, 02:07 AM
bluenosebex bluenosebex is offline Mail merge from excel - need to create sheets and create a table Windows 8 Mail merge from excel - need to create sheets and create a table Office 2013
Novice
Mail merge from excel - need to create sheets and create a table
 
Join Date: Jul 2015
Posts: 3
bluenosebex is on a distinguished road
Default

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
Reply With Quote
  #4  
Old 07-29-2015, 02:15 AM
macropod's Avatar
macropod macropod is offline Mail merge from excel - need to create sheets and create a table Windows 7 64bit Mail merge from excel - need to create sheets and create a table Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,224
macropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant future
Default

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]
Reply With Quote
  #5  
Old 08-01-2015, 05:44 AM
bluenosebex bluenosebex is offline Mail merge from excel - need to create sheets and create a table Windows 8 Mail merge from excel - need to create sheets and create a table Office 2013
Novice
Mail merge from excel - need to create sheets and create a table
 
Join Date: Jul 2015
Posts: 3
bluenosebex is on a distinguished road
Question Got it! well nearly... tables pull info through but won't update?

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}
Reply With Quote
  #6  
Old 08-02-2015, 05:34 PM
macropod's Avatar
macropod macropod is offline Mail merge from excel - need to create sheets and create a table Windows 7 64bit Mail merge from excel - need to create sheets and create a table Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,224
macropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant future
Default

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:
Code:
{DATABASE \d "W:\\XXXXX\\XXXXX\\Trial MM data RH.xlsx" \s "SELECT `Building`, `Floor`, `Room/Area` FROM `Sheet1$` WHERE `Extent of damage score` = 0 ORDER BY `Sample`" \l "23" \b "191" \h}
or, if the document and data source are stored in the same folder:
Code:
{DATABASE \d "{FILENAME \p}/../Trial MM data RH.xlsx" \s "SELECT [Building], [Floor], [Room/Area] FROM [Sheet1$] WHERE [Extent of damage score] = 0 ORDER BY [Sample]" \l "23" \b "191" \h}
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]
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Using Mail Merge to create summary report in ONE document ChandaS Mail Merge 1 12-19-2014 10:16 PM
Mail merge from excel - need to create sheets and create a table Would like to mail merge excel spreadsheet with word doc and create new page when a catagory changes esherwood Mail Merge 1 07-21-2014 09:55 PM
Mail merge from excel - need to create sheets and create a table How to use mail merge to create a list sorted by category in Word elisa2013 Mail Merge 5 03-14-2014 02:35 AM
Mail merge from excel - need to create sheets and create a table Urgent Supprt Need to Create Mail Merge letter risnasmhd Mail Merge 1 09-29-2013 08:11 PM
Mail merge from excel - need to create sheets and create a table Mail Merge to create specific number of table rows flackend Mail Merge 2 08-24-2011 11:49 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 08:25 PM.


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