#1
|
|||
|
|||
I want to create a lists within lists (nested merges)
I have Microsoft office for Mac 14.5.8 running on OS X 10.11.1.
I have a basic level of VBA programming skills. My requirement is to generate a WORD document cataloguing a number of artworks with, for each work, a page containing details of the work and a list of the exhibitions it has been in. The first bit I can do with mailmerge, but I don’t know how to make the embedded list of exhibitions for each work. I want to create a line for each exhibition, so it is like a mailmerge-within-a-mailmerge. Is this possible with VBA? (I don’t necessarily have to use mailmerge if there is a better way). I currently have an EXCEL workbook functioning as a database for the artworks with a table on each worksheet (tab). This could be migrated to SQLLite or similar if that helps. Exhibitions can tour, so location is a separate table; an exhibition will take place at one or more locations, so ideally I want to list the locations for each exhibition as well, giving two levels of embedding. Tables:
|
#2
|
||||
|
||||
Are there multiple records per artwork, or are the exhibitions all part of the same record? In Excel 1 row = 1 record.
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#3
|
|||
|
|||
The tables are designed along relational lines...
I do not need a complete solution; mainly I need an indication of how one can merge a sub-list within a list. |
#4
|
||||
|
||||
OK, a mailmerge can only reference a single data source (in this case, a single worksheet). That said, if that worksheet provides keys to the others, you could combine the mailmerge with a DATABASE field to retrieve data from the other sheets and output the data in a table format. Using a DATABASE field requires a working knowledge of SQL.
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#5
|
|||
|
|||
Mmm. Thanks for confirming my understanding so far.
I can handle SQL, but I am not sure what you mean by a DATABASE field, or how it will help me? Do you mean the Get external data from a database feature? If so, then I also need to instal an ODBC driver and have not yet found a compatible one. I have an idea that might move me forward, but still insufficient detail to implement. I could:
|
#6
|
||||
|
||||
Word has numerous types of field, one of which is the DATABASE field. For a comprehensive list, go to Insert|Quick Parts|Field.
For a non-mailmerge implementation of a DATABASE field, see: https://www.msofficeforums.com/mail-...html#post67110
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#7
|
|||
|
|||
Ah! I was confused, I thought you were talking about a 'DATABASE' field in EXCEL!!
Yes, in WORD for Mac there are no 'Quick parts' but one can insert a DATABASE field with Insert -> Field -> Mailmerge -> Database. I guess you are suggesting I create the Exhibitions table to look like a database (even in EXCEL?), then from the Mailmerge master document use a DATABASE field with an SQL query to generate the list of exhibitions for each Object? Can I use Object_id , the key to the record in the Mailmerge Object table, as a parameter in the query? The SQL query would then do "Get me a table of all the exhibitions belonging to this object"? I see there are some table formatting options in the field, but it is going to take some experimentation to get it right. This does look like it might be a bit complicated for my relatively small application (119 records). What do you think of my suggestion in the previous post, to use intermediate mini-documents, themselves created by a mail merge with a bit of VBA to control the loop that creates an Exhibitions mini-document for each Object? I think this might give me more flexibility on formatting? |
#8
|
||||
|
||||
OK, so you have -
Quote:
Although the DATABASE field outputs its results as a table, you could reformat the table or throw it away (just keeping the data) afterwards. And, depending on your requirements, you could have multiple DATABASE fields, with their own outputs for each record returned from the Object table.
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#9
|
|||
|
|||
Paul,
This is starting to look do-able, though I still need some detail, so I am going to present a simplified example in the next post. First, here, the story so far… I followed the link you provided in a previous post, which gives a very helpful example of the use of a DATABASE field. In that example, three database queries are executed and each allocated to a table, but it only produces one letter. I need to combine the results of the query with a mailmerge. The Microsoft article on ‘Catalog’ mail merging seems to make that connection, but WORD does not have multiple levels built in (to nest Objects, Exhibitions and Locations). I then found your posting on “many to one” cataloging. The examples look close to what I want to achieve, but when I installed the .dotm file on my system (WORD 2011 for Mac), it did not work. I got nothing in the toolbar and WORD continued working a one level, i.e. made a whole new record for each Exhibition and Location. Is the plugin compatible with WORD for Mac? There is a Zotero.dotm in the same folder and that works OK. From these and other postings, I see that things become clearer with an example, so I have created one in the next post: the Catalogue Mailmerge Challenge. Andy |
#10
|
|||
|
|||
Andy’s catalogue mailmerge challenge
The attached EXCEL table (Sheet1) is a “flattened” version of a relational database. This would normally be generated as a query from the actual database, which is currently an EXCEL workbook, but this one was made by hand for test purposes to avoid being distracted by the details of the database.
I want to generate a WORD document with a section (new page) for each Object. (An Object is a work of art, drawing, painting, sculpture etc). At the top of the section is the fixed singular data for the object: Artist, Title, Year, Dimensions and Materials. Then there is a section headed Exhibitions, which lists all the Exhibitions (if any) in which the Object has appeared, in chronological order of (first) Start date, giving the name of the Exhibition and the Catalogue_nr of this Object at that Exhibition. If the Object has never been exhibited then the text “No exhibition record” should be entered instead. For each Exhibition there is a list of Locations with Start and End dates, in chronological order of Start date. There will often only be one Location, but touring exhibitions can have several. The first one should appear on the same line as the Exhibition details, and others on subsequent lines with blanks or ditto marks for the Exhibition details. The attached WORD file is a start, but not complete. The rules are:
Who can rise to this challenge and show me how to make this simple example work on my computer, with Mac OS X 10.11.1 and Microsoft Office for Mac 2011 version 14.5.8? |
#11
|
||||
|
||||
A directory/catalogue mailmerge working with your flattened workbook would be possible but, as is evident in the tutorial, adding extra subgroup keys increases the complexity. For two keys (e.g. Object_id and Exhibition_id), the examples in the tutorial would already suffice. For some worked examples, see the attachments to the posts at:
https://www.msofficeforums.com/mail-...html#post23345 https://www.msofficeforums.com/mail-...html#post30327 http://windowssecrets.com/forums/sho...l=1#post928391 Alternatively, you may want to try one of the Many-to-One Mail Merge add-ins, from: Graham Mayor at http://www.gmayor.com/ManyToOne.htm; or Doug Robbins at http://bit.ly/1hduSCB That said, the advantages of the DATABASE field include not having to flatten the workbook structure and automatically generating a table for the output. On its own, a directory/catalogue mailmerge doesn't work with multiple sheets and can't directly produce a single table for each group or subgroup.
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
Tags |
list within list, nested merge, vba mail merge |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Cant create any more numbered lists. | rapid3642 | Word | 9 | 11-14-2014 08:28 PM |
Need Serious Help with Outlines/Lists | Stone | Word | 5 | 09-11-2012 04:56 PM |
Numbered Lists | jburtis | Word | 1 | 04-07-2012 02:42 PM |
Distribution Lists | WildBill | Outlook | 2 | 08-11-2011 08:15 AM |
Searching dates to create lists | brianh | Excel | 2 | 01-18-2010 01:58 PM |