Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 01-10-2020, 04:49 AM
Danlisa Danlisa is offline Mail Merged Letter needs Dynamic Table Populated from Excel Windows 7 64bit Mail Merged Letter needs Dynamic Table Populated from Excel Office 2010
Novice
Mail Merged Letter needs Dynamic Table Populated from Excel
 
Join Date: Jan 2020
Location: Cornwall, UK
Posts: 2
Danlisa is on a distinguished road
Default Mail Merged Letter needs Dynamic Table Populated from Excel

Unsure if this is a Word or Excel query. Inquiring to see if I can even achieve this....



I have a spreadsheet with 3 populated tabs (addresses, collection specifics & product coding). The spreadsheet uses VLOOKUP to auto-populate cells in the Collections tab from the Addresses tab when a user inputs a unique Account Ref, it also auto-populates info from the Product Coding tab into the Collection tab based on what was collected.

Onto WORD - I have setup the standard mail merge for the address mailout and included a field reference to the specific/unique Account Ref used in the VLOOKUP.
However I also need a dynamically resizing table in the body of the letter to 'search' the Collection Tab of the spreadsheet (by referencing all entries that contain the unique Account Ref by default Column A) and then populate the table with data from 5 or 6 cells also from the Collection Specifics tab of the spreadsheet but to include ALL instances where the Unique Account Ref appears in Column A on the Collections tab arranged by date.

To give you a better idea, this is a letter report that details 3 months worth of collections from a customer list (some 4000 strong). Some customers may have 3 collections, while other may have 500+ within the quarter period. I hope I have explained this adequately but have uploaded a screengrab of the current word layout.

To say I'm a novice at this stuff might be an understatement but I'm willing to put time to understand it.....just go easy on the technical jargon.

Thanks for your time.
Attached Images
File Type: jpg CULetter.jpg (74.3 KB, 33 views)
Reply With Quote
  #2  
Old 01-10-2020, 02:46 PM
macropod's Avatar
macropod macropod is offline Mail Merged Letter needs Dynamic Table Populated from Excel Windows 7 64bit Mail Merged Letter needs Dynamic Table Populated from Excel 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

Since you're drawing data from different worksheets, you'll need to use a DATABASE field. An outline of this approach can be found at:
Mail Merge - To a Word Table on a Single Page - Microsoft Community
For some working examples, see:
Mail Merge: Using One Excel File with Multiple Sheets
Merge excel list into Word Receipt
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #3  
Old 01-13-2020, 06:46 AM
Danlisa Danlisa is offline Mail Merged Letter needs Dynamic Table Populated from Excel Windows 7 64bit Mail Merged Letter needs Dynamic Table Populated from Excel Office 2010
Novice
Mail Merged Letter needs Dynamic Table Populated from Excel
 
Join Date: Jan 2020
Location: Cornwall, UK
Posts: 2
Danlisa is on a distinguished road
Default

Thank you.

Using your example from Mail Merge: Using One Excel File with Multiple Sheets

I adapted the database code to use my filenames and column headings etc. as below:

Code:
DATABASE \d "Y:\EA Haz Database\New Haz Database Oct - Dec 2019\Test Files\Haz Waste Cover Letter.docx/../DB Info.xlsx" \s " SELECT FORMAT([WHEN], 'DD-MM-YYYY') AS `Date`, [CONSIGN], Format([QTY],'#,##0.00') AS Quantity KG, [DESCRIPTION] AS Description, [ITEM] AS `EWC`, [CLASS] AS Disposal Method FROM [Detail$] WHERE [ID] = Account Ref ORDER BY [WHEN] " \l "15" \b "49" \h
A few things fail.
The Table headers and contents do not alter from your original layout, however when viewing the Field Code the WHERE ID does alter with each record previewed but I suspect this is just working down my Customer Address Tab.

If I ask WORD to update the fields or run a simulation I get "This data source can not be opened because it is not supported by the application".

I also receive the error "The SELECT statement includes a reserved word or argument...."

Also, after manually editing the code, on occasion the table doesn't render at all but the code is present.

This might be above my pay grade! LOL

If I persevere with this, is there a Condition or Argument where the merge only happens if there is an entry within the DB Info.xlsx that will populate the table? I have more Customer Addresses than I do Customers with Collections and don't need to print "blank" letters.
Reply With Quote
  #4  
Old 01-13-2020, 03:29 PM
macropod's Avatar
macropod macropod is offline Mail Merged Letter needs Dynamic Table Populated from Excel Windows 7 64bit Mail Merged Letter needs Dynamic Table Populated from Excel 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 Danlisa View Post
The Table headers and contents do not alter from your original layout, however when viewing the Field Code the WHERE ID does alter with each record previewed but I suspect this is just working down my Customer Address Tab.
It is not apparent to me what you mean by this.
Quote:
Originally Posted by Danlisa View Post
If I ask WORD to update the fields or run a simulation I get "This data source can not be opened because it is not supported by the application".
Your:
"Y:\EA Haz Database\New Haz Database Oct - Dec 2019\Test Files\Haz Waste Cover Letter.docx/../DB Info.xlsx"
is invalid. Did you look at the actual field code in the document attached to that link?
Quote:
Originally Posted by Danlisa View Post
I also receive the error "The SELECT statement includes a reserved word or argument...."
your:
Quantity KG, Description, Disposal Method, and Account Ref
are not properly quoted. See the sample field in the link.
Quote:
Originally Posted by Danlisa View Post
Also, after manually editing the code, on occasion the table doesn't render at all but the code is present.
That only means the field code is incorrect.
Quote:
Originally Posted by Danlisa View Post
If I persevere with this, is there a Condition or Argument where the merge only happens if there is an entry within the DB Info.xlsx that will populate the table? I have more Customer Addresses than I do Customers with Collections and don't need to print "blank" letters.
If there is a field on whatever worksheet you're using as the primary one that gives an indication, you could enclose the entire DATABASE field in an SKIPIF field that only outputs the DATABASE if the SKIPIF test is satisfied. Similar results might be obtained using the mailmerge filters.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Mail Merged Letter needs Dynamic Table Populated from Excel Mail merged document and page numbers for table of contents jab40 Mail Merge 20 04-07-2016 08:25 AM
Mail Merged Letter needs Dynamic Table Populated from Excel Mail merge with dynamic table kidwispa Mail Merge 3 07-06-2015 02:00 PM
Dynamic Mail Merged Table hanskimber Mail Merge 2 04-24-2015 11:21 PM
Mail Merged Letter needs Dynamic Table Populated from Excel Mail Merge Into Dynamic Table Jag618 Mail Merge 1 03-04-2013 11:26 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 04:32 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