Results 1 to 6 of 6
  1. #1
    Lounger
    Join Date
    Dec 2011
    Posts
    27
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Userform for mail merge (Excel): single record, multiple documents

    Hi uber geniuses!

    I've got a couple of queries, but I've separated them across different threads - I hope that's ok! I thought it would make it easier.

    For this one, I need to be able to have the user enter in a row number from the Excel spreadsheet, and then have them select which documents they need to create for that record. I then need each document to open in a separate window (ie, not merged into the one document as Word usually does).

    I have set up the basic userform with a button (frmMerge), but Iím not sure where to go from here Ė at a complete loss unlike the other userform, where I at least have something! Is this even possible? Iíve tried looking through the earlier posts here, and there seem to be some that might be similar, but Iím such a beginner that Iím not sure what Iím looking at. Sorry!

    I've attached my test database and two test documents with some example merge fields so you can see what I mean. I know my coding is horrible - forgive me, I'm a beginner!

    Help!.zip

  2. Subscribe to our Windows Secrets Newsletter - It's Free!

    Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!

    Excel 2013: The Missing Manual

    + Get this BONUS — free!

    Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!

  3. #2
    Super Moderator
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    3,503
    Thanks
    3
    Thanked 141 Times in 134 Posts
    Caracolesa

    Well this is a pretty big job to start with so the good news is that you are going to learn lots of good stuff doing this project. The bad news is there is a lot of work required before this little baby is up and running.

    However, maybe we should go back a few steps and try to work out what you want us to do exactly. Do you want to display a combobox which contains the list of the records in Sheet 1? Then, the user chooses one and the other fields get filled from the corresponding field in Sheet 1? Is that what you want to do?

    Where then does the list of documents come from? Is this also in the workbook or is it the names of the TestDocs which might be sitting in a directory somewhere?

    You mentioned merge to individual docs/one doc. Are you intending to select more than one OMB Agent at a time? Or is this intended to produce a doc for each Director/Guarantor associated with that OMB Agent? If this is the case, I would recommend you restructure your data table to get a relational data structure going (best done in Microsoft Access but doable in Excel).
    Andrew Lockton, Chrysalis Design, Melbourne Australia

  4. #3
    Lounger
    Join Date
    Dec 2011
    Posts
    27
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Hi Andrew!

    Excellent! I love learning new things!

    Ok, so this is the basis of the project: the client has a suite of documents, which are all kept in the same directory as this spreadsheet. The documents are not identical. For example, one might be a lease, one might be an instruction sheet, one might be general conditions clauses, etc.

    They create one or more of the documents for each of the OMB Agents in the spreadsheet. All of the columns are merge fields in one or more of the documents. There are no columns in the sheet which aren't a merge field somewhere.

    I was trying to make it easier for them to be able to complete merges. At the moment, they are using Word's standard mail merge to select one OMB Agent, and merge the data associated with it into one of the documents, then repeating that for every document they need to merge that OMB Agent's info into.

    So, what I want them to be able to do is to pick which OMB Agent they want to create documents for (say, row 2 on the spreadsheet), then have a screen pop up pointed at the directory where the suite of documents is held, and have them be able to multiselect whichever documents they need to create for this particular OMB Agent, and then merge that row's data into all of those documents, outputting the documents individually, not as a single document.

    Does this make sense?

    I have some code for multiselecting files from a directory (I bought John Walkenbach's Excel VBA book), but I have no idea how to utilise it.
    Last edited by caracolesa; 2012-09-12 at 19:01. Reason: clarity

  5. #4
    Super Moderator
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    3,503
    Thanks
    3
    Thanked 141 Times in 134 Posts
    I'm sure you can come up with a slick implementation for this requirement but I'm not sure that a merge document is the way to do it. The problem with using merge fields is we can't dynamically add fields (without using VBA) just because this particular record has an entry for it. You can nest the mergefield in an IF field to allow you to not display empty fields (eg less than the maximum number of directors) I guess but this is going to be clunky if the content sits in a table or doesn't include paragraph marks etc.

    Since you are already using VBA for a bunch of this, it is probably easier to put together the documents using VBA automation from Excel and forget about using merge fields. This does require you to know a bit about the target document however so is probably not super scalable for them to add new document types and have your code work out the rest. Another alternative is to export the chosen data source in XML and then use a bit of code that embeds this xml into the document and displays the entries using Content Controls.

    The more I think about this problem, the more I think your source data should be relational tables held in an Access Database. You will be able to then build sql queries in different formats to allow flexibility in how a merge template places the data. Then it may be as simple as linking the template to a particular query in order to get flexibility in your template formats.
    Andrew Lockton, Chrysalis Design, Melbourne Australia

  6. #5
    Lounger
    Join Date
    Dec 2011
    Posts
    27
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Thanks for your reply, Andrew. I'll have a play with a few different options then, and see what I can come up with.

    Thanks!

  7. #6
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    3,855
    Thanks
    0
    Thanked 176 Times in 162 Posts
    Personally, I'd approach this in a completely different way, using LINK fields from the Word documents to the Excel workbook.

    The simplest would be to have a reference range in Excel that's always the same and you just use a formula for pulling in the data from the relevant rows (which could be on another sheet or even in another workbook). From there, it's just a matter of opening the target documents, updating & unlinking the fields (if the document is to be saved), before doing whatever you want with the output.

    Slightly more complicated from a programming perspective would be to forego the constant reference range and, instead, update the cell references in the LINK fields.
    Cheers,

    Paul Edstein
    [MS MVP - Word]

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •