Excel to Word merge

[Jo8379]

I'm trying to merge info from a excel spreadsheet with 15 fields into a word
document. I want all answers to question 1 listed together, then all answers
to question 2 listed together, etc. In the word doc I inserted the field
names, but when I merge I only get one record per page - and many, many pages
- like a letter rather than a a compiled list (as I described above). What am
I doing wrong? Is there a switch that I need to add?

 

[Peter Jamieson]

By default, Word sets up a merge as a "Letter merge" where Word starts a
new page for each record in the data source.

What you probably need is to set up your merge as a "Directory merge"
where Word does not start a new page for each record.

In Word 2007, you can choose the merge type in the "Start Mail Merge"
dropdown in the Mailings tab.

In Word 2002/2003, you can define it in the Mail Merge Wizard (it's one
of the early steps), or you can use View->Toolbars to display the Mail
Merge toolbar and click the first button.

In earlier versions of Word the thing you need is called a "Catalog
merge" and you can specify it in Step 1 of the Mail Merge Helper.


All that said, that will only get you what you want if your Excel sheet
is structured so that "all answers to question 1" are in record 1 of the
sheet, "all answers to question 2" are in record 2, etc. You don't
actually say how your sheet is organised, so if it isn't like that,
perhaps you could spell it out.

Peter Jamieson

http://tips.pjmsn.me.uk

 

[Jo8379]

Thank you for the reply. I've adjusted the setting to be "Directory" but it
did not help. I am exporting data from SharePoint into an Excel spreadsheet
where the first row is the heading: Answer 1, Answer 2, Answer 3 and so on.
Each answer one is in the column labeled Answer 1, etc. When I export the
data it saves as a different file type, I then open that file and save that
as a spreadsheet. Could that source file be the issue?

 

[Peter Jamieson]

Well, in order to group the data in Word so that all the answers to
question 1 are grouped together, and so on, you really need the sheet to
be the other way around. However, if there are a lot of answers, even
doing that will not work as a Mailmerge data source because Word can
only "see" a maximum of 255 columns (or 127, I forget which right now)

In other words, I think what you "really" have is something like

q1 q2 q3 ...
q1answer1 q1answer1 q1answer1 ...
q1answer2 q2answer2 q3answer2 ...
q1answer3 q2answer3 q3answer3 ...
q1answer4 q2answer4 q3answer4 ...

..
..

Whereas to mailmerge in the way that you want, what you really need is
either something more like

question answer1 answer2 answer3 answer4
q1 q1answer1 q1answer2 q1answer3 q1answer4
q2 q2answer1 q2answer2 q2answer3 q2answer4
q3 q3answer1 q3answer2 q3answer3 q3answer4
..
..
..

or a single column containing

q1
q1answer1
q1answer2
q1answer3
q1answer4
q2
q2answer1
q2answer2
q2answer3
..
..
..

I think you could achieve either of these structures in Excel. e.g.,

As long as you do not have too many rows, you could transpose the data
in Excel and add the new header row (i.e.

question answer1 answer2 answer3 answer4

to give you the first layout.

Then you would still do a Directory merge, but with layout2 you would
have to add some stuff to get the page breaks between each question (if
that's what you want).

I'm not sure how to get the second layout and have to dash right now, so
maybe you could think about how to do that in Excel.

Alternatively, even with your original layout you could consider
inserting 15 database fields or Linked objects, each containing the data
from each column. But whether you can do tht successfully depends on
what else you need in your document and how much control you want over
the layout.

Peter Jamieson

http://tips.pjmsn.me.uk