PDA

View Full Version : Multiple-Record Word Mail Merge



Anne Troy
05-23-2004, 02:11 PM
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/cindymeister/mergfaq1.htm#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?

mdmackillop
06-09-2004, 01:08 PM
Hi Dreamboat,
Its still a bit clunky but ...
Worth pursuing?

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

:type

Anne Troy
06-09-2004, 01:28 PM
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
06-09-2004, 01:30 PM
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".

mdmackillop
06-09-2004, 01:41 PM
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
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

Anne Troy
06-09-2004, 01:45 PM
Cool. Learn how to use our VBA tags here (http://www.vbaexpress.com/vbatags.htm), MD. So great to have you here!

Anne Troy
06-16-2004, 07:27 AM
Okay. I've copied this over to the Word help area. Let's see if we can get more help with it.

mdmackillop
07-01-2004, 12:52 PM
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

mdmackillop
07-11-2004, 12:48 PM
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

smilla
08-04-2004, 03:18 AM
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

mdmackillop
08-04-2004, 11:01 AM
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
Private Sub UserForm_Initialize()
ListBox1.AddItem "AuthName"
ListBox1.ListIndex = 0
OptionButton1 = True
End Sub



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.

'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


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

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