|
|
|
date: Wed, 6 Aug 2008 06:03:00 -0700,
group: microsoft.public.word.mailmerge.fields
back
Re: Excel to Word merge
You might think that it could be simpler, but what you are wanting to do is
perform a "multiple items per condition (=key field)" mailmerge which Word
does not really have the ability to do:
See the "Group Multiple items for a single condition" item on fellow MVP
Cindy Meister's website at
http://homepage.swissonline.ch/cindymeister/mergfaq1.htm#DBPic
Or take a look at the following Knowledge Base Article
http://support.microsoft.com/default.aspx?scid=kb;en-us;211303
or at
http://cornell.veplan.net/article.aspx?&a=3815
Or, if you create a Catalog (on in Word XP and later, it's called Directory)
type mailmerge main document with the mergefields in the cells of a one row
table in the mailmerge main document with the keyfield in the first cell in
the row and then execute that merge to a new document and then run the
following macro, it will create separate tables with the records for each
key field in them. With a bit of further development, you may be able to
get it to do what you want.
' Macro to create multiple items per condition in separate tables from a
directory type mailmerge
Dim source As Document, target As Document, scat As Range, tcat As Range
Dim data As Range, stab As Table, ttab As Table
Dim i As Long, j As Long, k As Long, n As Long
Set source = ActiveDocument
Set target = Documents.Add
Set stab = source.Tables(1)
k = stab.Columns.Count
Set ttab = target.Tables.Add(Range:=Selection.Range, numrows:=1,
numcolumns:=k - 1)
Set scat = stab.Cell(1, 1).Range
scat.End = scat.End - 1
ttab.Cell(1, 1).Range = scat
j = ttab.Rows.Count
For i = 1 To stab.Rows.Count
Set tcat = ttab.Cell(j, 1).Range
tcat.End = tcat.End - 1
Set scat = stab.Cell(i, 1).Range
scat.End = scat.End - 1
If scat <> tcat Then
ttab.Rows.Add
j = ttab.Rows.Count
ttab.Cell(j, 1).Range = scat
ttab.Cell(j, 1).Range.Paragraphs(1).PageBreakBefore = True
ttab.Rows.Add
ttab.Cell(j + 1, 1).Range.Paragraphs(1).PageBreakBefore = False
For n = 2 To k
Set data = stab.Cell(i, n).Range
data.End = data.End - 1
ttab.Cell(ttab.Rows.Count, n - 1).Range = data
Next n
Else
ttab.Rows.Add
For n = 2 To k
Set data = stab.Cell(i, n).Range
data.End = data.End - 1
ttab.Cell(ttab.Rows.Count, n - 1).Range = data
Next n
End If
Next i
--
Hope this helps.
Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.
Doug Robbins - Word MVP
"Jo8379" wrote in message
news:0788476E-D790-4DFE-8920-E48EB56A512E@microsoft.com...
> Thanks. I think it might be simpler than that. It's a survey summary.
> So I
> have a word document survey and I have the questions listed (1-10). After
> Question 1 in the survey form I'd like all responses to Question 1 to
> appear
> in a list. Then it will have question 2. Under that question I want to
> see
> all responses to question 2 listed.
>
> By the way - I'm using 2003.
>
>
> "macropod" wrote:
>
>> Hi Jo8379,
>>
>> See: http://support.microsoft.com/?kbid=211303
>> Note that the field code construction in that article results in an extra
>> line being inserted between records when the data source
>> is an Excel workbook.
>>
>> The following (simpler) field coding eliminates the unwanted extra lines
>> from the first example:
>> {QUOTE{IF{MERGESEQ}= 1 {SET Place ""}}"{IF{MERGEFIELD City}<> {Place}
>> "{IF{MERGESEQ}= 1 "" "<ENTER>
>> "}{MERGEFIELD City}{SET Place {MERGEFIELD City}}<ENTER>
>> <ENTER>
>> "}{MERGEFIELD Employee}<TAB>{MERGEFIELD Sales}<ENTER>
>> "}<ENTER>
>> Modify the MERGEFIELD field names to suit your data.
>>
>> --
>> Cheers
>> macropod
>> [MVP - Microsoft Word]
>>
>>
>> "Jo8379" wrote in message
>> news:DF12F9AD-2EE5-4C28-87F8-67A910E1AE07@microsoft.com...
>> >I have an excel spreadsheet of data that I'm trying to merge into a
>> >word
>> > document (a survey). I'd like all responses to Question 1 to be listed
>> > under
>> > Question 1 in the word document. I have entered the merge field name
>> > for
>> > Question one, two, etc. but I'm only getting the first survey results
>> > in the
>> > output doc. How do I get the rest to appear?
>>
>>
date: Thu, 7 Aug 2008 05:36:59 +1000
author: Doug Robbins - Word MVP
|
|
