Consulting

Results 1 to 11 of 11

Thread: Multiple-Record Word Mail Merge

  1. #1
    Site Admin
    The Princess
    VBAX Guru Anne Troy's Avatar
    Joined
    May 2004
    Location
    Arlington Heights, IL
    Posts
    2,530
    Location
    Some of these posts were copied from here:

    http://www.vbaexpress.com/forum/showthread.php?t=76

    We now need help with a portion of it, so I've copied the pertinent posts into the Word help area.

    _________________________________________________


    Word's mailmerge feature has a terrible failing.

    You cannot tell it you want to merge multiple records from one data source. In other words, if I have a list with:

    clientname
    invoice no
    invoice date
    invoice amount

    I cannot create a mail merge that will put all of client one's invoices on one page. Cindy Meister shows how to do it here, but I'll be darned if I've ever gotten it to work, and at the moment, can't even get to that page:

    http://homepage.swissonline.ch/cindy...tm#ComplexMerg

    So, what I would have loved all these years is a wizard that will let me do this. It could simply grab and display the headings in the data source file, whatever that data source may be, and allow the user to pick which heading to group by--much like an Access report.

    If someone can do this, it'd be worth (easily) $50 a pop right here from our web.

    Do we need a sample file?
    Last edited by Anne Troy; 06-16-2004 at 07:26 AM.
    ~Anne Troy

  2. #2
    Administrator
    VP-Knowledge Base VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,489
    Location
    Hi Dreamboat,
    Its still a bit clunky but ...
    Worth pursuing?

    Save all files into C:\AMerge; open MergeAll
    MD


  3. #3
    Site Admin
    The Princess VBAX Guru Anne Troy's Avatar
    Joined
    May 2004
    Location
    Arlington Heights, IL
    Posts
    2,530
    Location
    Well. It's really terrible, MD.

    Your code didn't work!!!

    I had to change the code to go to Amerge instead of Atest.

    AND THEN IT WORKED BEAUTIFULLY.

    I just want to check out the rest of the files.
    ~Anne Troy

  4. #4
    Site Admin
    The Princess VBAX Guru Anne Troy's Avatar
    Joined
    May 2004
    Location
    Arlington Heights, IL
    Posts
    2,530
    Location
    It's freaking gorgeous.
    Now, do we leave it as is?
    Or attempt to create some kind of template to allow people to build this thing for their custom purposes?
    Do we make this a project where you get some help and you be Proj Mgr?
    What do you want to do today?
    Where do you want to go today?
    I love this MD
    Do you have ANY idea how many people would kill for it?
    They just do NOT want to hear "use Access".
    ~Anne Troy

  5. #5
    Administrator
    VP-Knowledge Base VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,489
    Location
    It works fine with small data, but with hundreds of records it gets too slowly. I posted the following in "another place", but have not yet resolved this problem. Any assistance would be appreciated.
    MD

    I?ve created some code which is attached to a word document to do a compound merge. The database source is in an RTF file, and the first field (of 8 fields) in this document contains data from the ClientID autonumber field in my database. As there are a number of sub-records attached, each ClientID is repeated one or more times, in ascending order. The following code creates an array RS(i,x), where ?i? increments and ?x? is the count of each ClientID entry.
    While the code works well for 20 ? 30 records, it takes a long time (70-80 secs.!) to loop through 256 records to create the array. It starts off quickly enough, but slows down exponentially as it progresses (running out of steam, I guess). Can someone suggest a quicker way to create the array. I don?t merge directly from the database because that is very much slower.
    MD

    'Create array of number of sub-records under each main record
    [vba]Do
    With ActiveDocument.MailMerge.DataSource
    .ActiveRecord = wdNextRecord
    If .ActiveRecord = Num Then GoTo ExitLoop
    Num = .ActiveRecord
    RecNo = ActiveDocument.MailMerge.DataSource.DataFields("ClientID").Value
    If RecNo <> Tmp Then
    i = i + 1
    RS(i, 1) = i
    RS(i, 2) = Num
    Tmp = RecNo
    End If
    End With
    Loop
    ExitLoop:
    MsgBox msg[/vba]

  6. #6
    Site Admin
    The Princess VBAX Guru Anne Troy's Avatar
    Joined
    May 2004
    Location
    Arlington Heights, IL
    Posts
    2,530
    Location
    Cool. Learn how to use our VBA tags here, MD. So great to have you here!
    ~Anne Troy

  7. #7
    Site Admin
    The Princess VBAX Guru Anne Troy's Avatar
    Joined
    May 2004
    Location
    Arlington Heights, IL
    Posts
    2,530
    Location
    Okay. I've copied this over to the Word help area. Let's see if we can get more help with it.
    ~Anne Troy

  8. #8
    Administrator
    VP-Knowledge Base VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,489
    Location

    Multiple Merge Simplified

    An attempt to simplify things.
    Depends upon
    1. ID field named Rec for each addressee, sorted to keep together.
    2. Sufficient NextRecord fields in the mailmerge document.
    3. Folder named C:\Amerge; subfolder named ThingsToPrint

    Works by
    1. Creating array of ID records
    2. Filtering merge data for each record
    3. Creating new document for each record to be saved/printed or whatever
    4. The attached database file will create the MergeData.rtf file used as the datasource.

    MD

  9. #9
    Administrator
    VP-Knowledge Base VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,489
    Location

    Question Excel DataSource problem

    Dreamboat, getting closer to your original request.

    But...
    Trying to work with different DataSources, Word and Excel, I get 2 different results
    Whereas an RTF Datasource is consistent, with an Excel DataSource, I lose the WHERE part as follows: Any suggestions?
    strRec = "SELECT * FROM " & DFile & " WHERE " & MyID & " = " & GRec
    ActiveDocument.MailMerge.DataSource.QueryString = strRec
    Debug.Print strRec
    Debug.Print ActiveDocument.MailMerge.DataSource.QueryString

    Immediate Window:
    SELECT * FROM C:\AMerge\MergeData.xls WHERE Surname = MacKillop
    SELECT * FROM C:\AMerge\MergeData.xls

  10. #10
    VBAX Regular
    Joined
    Jul 2004
    Posts
    19
    Location

    Just a couple of questions regarding AMerge

    Hi there,

    I just wanted to say thank you for putting this on the forum, I have been racking my brain for weeks about this one.

    I do have a question, only because I want to customise to my system.

    With the MergeAll2.doc, the userform1 works great but I don't need it. What I do need though is instead of choosing a field to sort on from the ListBox1, I want to sort on this field automatically (instead of using the form).

    Could you help me to rearrange the code so that it sorts of a mergefield called <<AuthName>> as string.

    Thanks again.

    Smilla

  11. #11
    Administrator
    VP-Knowledge Base VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,489
    Location

    MailMerge

    Hi Smilla, I don't have much time as I'm off on holiday tomorrow night. Here's a quick fix which may help.
    MD

    Replace the Form Initialize code as follows
    [VBA] Private Sub UserForm_Initialize()
    ListBox1.AddItem "AuthName"
    ListBox1.ListIndex = 0
    OptionButton1 = True
    End Sub

    [/VBA]

    Add the following sort routine where the code has the first line below. This should sort your data by Column 1. If AuthName is in a different column, try changing this accordingly.
    [VBA]
    'Open DataSource to get ID list
    Documents.Open DFile

    Selection.Tables(1).Select
    Selection.Sort ExcludeHeader:=True, FieldNumber:="Column 1", SortFieldType _
    :=wdSortFieldAlphanumeric, SortOrder:=wdSortOrderAscending, FieldNumber2 _
    :="", SortFieldType2:=wdSortFieldAlphanumeric, SortOrder2:= _
    wdSortOrderAscending, FieldNumber3:="", SortFieldType3:= _
    wdSortFieldAlphanumeric, SortOrder3:=wdSortOrderAscending, Separator:= _
    wdSortSeparateByTabs, SortColumn:=False, CaseSensitive:=False, LanguageID _
    :=wdEnglishUS
    Selection.HomeKey Unit:=wdStory
    [/VBA]

    I realise the form is still there, but I don't know which merge you were after.
    You could remove the user form if you called the NewMerge sub from another sub with the values set permanently as follows

    [VBA] Sub DoMerge()
    NewMerge "AuthName", "S", "TempFolder" 'M/P/S Change as appropriate
    End Sub
    [/VBA]

Posting Permissions

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