Results 1 to 6 of 6
  1. #1
    Lounger
    Join Date
    Apr 2004
    Location
    Durham, North Carolina, USA
    Posts
    32
    Thanks
    11
    Thanked 1 Time in 1 Post

    Incrementing a number as part of a mail merge

    Hi folks,

    I have a mail merge document which pulls clinic names, addresses, etc from an Excel worksheet. My problem is that for each clinic, I want to print multiple (>100) copies of their document without having multiple entries in the Excel sheet. Now, normally I would just print multiple copies, but the issue is that each document needs to have a sequentially-numbered code (done as a bar code).

    Here's an example:
    Clinic LSI
    Document 1
    ID code: LSI2013-1
    Document 2
    ID code: LSI2013-2
    ...
    Document 150
    ID code: LSI2013-150

    Then

    Clinic CGM
    Document 1
    ID code: CGM2013-1
    Document 2
    ID code: CGM2013-2
    ...
    Document 150
    ID code: CGM2013-150

    And then the next clinic.

    I see it as a two-fold problem. 1) How to generate multiple documents for a single mail-merge item. 2) For each document, how to generate a number that incorporates the Clinic ID AND increments.

    OK, maybe that's more than two problems.

    I'd love to hear ideas/thoughts/solutions. Thanks in advance for any help you can provide.

    Beej

  2. #2
    Lounger
    Join Date
    Apr 2004
    Location
    Durham, North Carolina, USA
    Posts
    32
    Thanks
    11
    Thanked 1 Time in 1 Post
    I'm replying to my own post because I've found a solution that will work, though it removes the mail merge step.

    I'll generate single documents for each clinic and save each as a template. Then using the code here: http://word.mvps.org/FAQs/MacrosVBA/NumberDocs.htm

    I'll generate the incremented code for each clinic.

    Then, using the code here: http://word.tips.net/T011236_Creatin..._One_Step.html

    I'll generate the hundreds of documents I need for each clinic.

    I think that will work. Thoughts?

  3. #3
    3 Star Lounger
    Join Date
    Feb 2001
    Location
    Willow Grove, Pennsylvania, USA
    Posts
    205
    Thanks
    4
    Thanked 49 Times in 40 Posts
    The only thing that concerns me about that plan is whether Word will behave nicely when you try to open 150 documents all at once. I suspect you'll run out of memory and crash long before that point.

    Instead, modify the word.tips code so that the For...Next loop adds a document, prints it, and closes it (either saving or not, your choice), before adding the next document. You might also need to throw in a few seconds of delay just before the end of the loop to let all the background activities (printing, saving) complete before going on.

  4. #4
    2 Star Lounger
    Join Date
    Dec 2009
    Location
    Dallas, Texas, USA
    Posts
    113
    Thanks
    3
    Thanked 6 Times in 6 Posts
    On the surface, it would appear that the "tidy" approach to this problem would be to hook the MailMergeBeforeRecordMerge event. Although it's still in my production code, however, I moved all of its code out of the event sink, and into the Document_New event procedure of ThisDocument.

    I moved the code because there are too many things that get lost in a merge, especially bookmarks stored in your template. Since Document_New sees the template and its bookmarks, you must do such things as inserting text into bookmarks in the event procedure, which runs before MailMergeBeforeRecordMerge gets the document.

    Be aware that getting this right is very tricky, especially when it comes time to close down. Open documents must close in the correct order, and that order depends on how the session starts. Following are notes that I made when I did this project, and left as a comment in the VBA ThisDocument module.

    If the only document remaining is the Primary Document, its closure must be deferred, or the loop will terminate prematurely.

    Its closure can be safely deferred because Application.Quit closes it cleanly as it shuts down Microsoft Word.

    When the template is opened directly, it is the Primary Document. You cannot close it without prematurely terminating this loop, leaving the merged document open.

    If all open documents were spawned by code in this template, the call to Application.Quit will dispose of the template.

    Finally, in all cases, the Document that corresponds to the ActiveDocument object reference must be closed last, or the loop terminates prematurely, leaving one or more documents stranded.
    Even if you don't need this information, hopefully at least one other person will benefit from it.
    David Gray, Chief Wizard
    WizardWrx
    Irving, Texas, USA

    WizardWrx Web - Technical Articles and Free Software
    You are more important than any technology we may employ.

  5. The Following User Says Thank You to TXWizard For This Useful Post:

    Beej (2013-02-19)

  6. #5
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts
    I'd suggest that, rather than worrying about the production of multiple copies of each mailmerge record, you might do better to use a field, coded as {=0}, for the incremental part of the number and a macro like the following to handle the incremental numbering for printout purposes.
    Code:
    Sub Demo()
    Application.ScreenUpdating = False
    Dim i As Long, j As Long, Fld As Field
    j = InputBox("How many copies to print?", "Print Numbering", 1)
    With ActiveDocument
      For i = 1 To j
        For Each Fld In .Fields
          If Fld.Type = wdFieldExpression Then Fld.Code = "=" & i
        Next
      Next
      .Fields.Update
      .PrintOut
    End With
    Application.ScreenUpdating = True
    End Sub
    If you're using a 3of9 barcode, all you need to do is to format the field with your barcode font.
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  7. The Following User Says Thank You to macropod For This Useful Post:

    Beej (2013-02-19)

  8. #6
    Lounger
    Join Date
    Apr 2004
    Location
    Durham, North Carolina, USA
    Posts
    32
    Thanks
    11
    Thanked 1 Time in 1 Post
    David and Paul, thank you both for your suggestions. As usually happens to me, my boss saw my solution, liked it, and wanted to add more functionality (the reward for work well done is more work). Your suggestions here have come in handy, as adding the functions he wanted made my original solution clunky. The "tidy" fix was much...tidier.

    You are both invaluable resources.

  9. The Following User Says Thank You to Beej For This Useful Post:

    TXWizard (2013-03-13)

Posting Permissions

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