Specify a header COLUMN instead of the usual header row in source

[Michael Fields]

Hello, I'm using Word 2007 trying to create a catalog-style merge, using an
Excel 2007 file as the data source. When I set it up, I get a checkbox that
allows me to specify if the 1st row contains column headers, y or n, which
works fine if my source Excel file is set up that way. But if it is, I run
out of columns.. Excel seems to allow only up to column 'IV.' so I want to
"transpose" my data, in Excel speak, to have headers specified in the first
COLUMN of Excel, and the records (which make up the eventual catalog1.doc,
catalog2.doc, etc resultant files) would be found in the entirety of columns
B, then column C, etc. So I'm trying to take advantage of the many more rows
than columns that Excel offers, which would solve the problem in my case. Can
anyone offer suggestions as to how I can tackle this?

Very much appreciated!

 

[Doug Robbins - Word MVP]

You are not going to be able to do that with mail merge. You would have to
create a "roll-your-own" equivalent using a macro that iterated through the
columns and rows, probably using DOCVARIABLE fields in the template in place
of merge fields and creating a new document from the template as it came to
each column and setting the value of the variables in that document to the
data contained in the cells in that column.

--
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

 

[macropod]

Hi Michael,

You mention Word 2007, so I guess you're using Excel 2007 as well. FWIW, Excel 2007 supports far more columns than previous
versions,

 

[Michael Fields]

Thanks to both of you very much!

 

[Peter Jamieson]

Yes, unfortunately despite Excel's increased capacity, none of the built-in
methods that Word MailMerge has to get data from Excel lets you see more
than 255 (or maybe 256) columns - unless something has changed recently.

One thing that may work is to create a new blank document in Word, then in
Excel, select the data, Edit->Copy (or Excel 2007 equivalent), then in Word
Paste->Special (click the arrow under the Paste button) and select, say,
Unformatted text or perhaps unformatted Unicode text. You should then end up
with the data in tab-delimited format within Word. Save that, and use it as
the data source for the merge.

It may not work if you have multiline data in Excel, and you may be prompted
for delimiter characters within Word, don't expect the Edit Recipients
dialog to work perfectly, etc.

--
Peter Jamieson
http://tips.pjmsn.me.uk

Hi Michael,

You mention Word 2007, so I guess you're using Excel 2007 as well. FWIW,
Excel 2007 supports far more columns than previous versions,

--
Cheers
macropod
[MVP - Microsoft Word]

Hello, I'm using Word 2007 trying to create a catalog-style merge, using
an
Excel 2007 file as the data source. When I set it up, I get a checkbox
that
allows me to specify if the 1st row contains column headers, y or n,
which
works fine if my source Excel file is set up that way. But if it is, I
run
out of columns.. Excel seems to allow only up to column 'IV.' so I want
to
"transpose" my data, in Excel speak, to have headers specified in the
first
COLUMN of Excel, and the records (which make up the eventual
catalog1.doc,
catalog2.doc, etc resultant files) would be found in the entirety of
columns
B, then column C, etc. So I'm trying to take advantage of the many more
rows
than columns that Excel offers, which would solve the problem in my case.
Can
anyone offer suggestions as to how I can tackle this?

Very much appreciated!

 

[macropod]

Hi Peter,

Given that you can't have more than 255 fields in a mailmerge, the ability to have more than 256 columns in a worksheet isn't much
use. If my data set had more than 256 columns, I'd probably want to insert a new worksheet as the mailmerge source, linked to just
the relevant columns in the data set.

--
Cheers
macropod
[MVP - Microsoft Word]

Yes, unfortunately despite Excel's increased capacity, none of the built-in methods that Word MailMerge has to get data from Excel
lets you see more than 255 (or maybe 256) columns - unless something has changed recently.

One thing that may work is to create a new blank document in Word, then in Excel, select the data, Edit->Copy (or Excel 2007
equivalent), then in Word Paste->Special (click the arrow under the Paste button) and select, say, Unformatted text or perhaps
unformatted Unicode text. You should then end up with the data in tab-delimited format within Word. Save that, and use it as the
data source for the merge.

It may not work if you have multiline data in Excel, and you may be prompted for delimiter characters within Word, don't expect
the Edit Recipients dialog to work perfectly, etc.

--
Peter Jamieson
http://tips.pjmsn.me.uk

Hi Michael,

You mention Word 2007, so I guess you're using Excel 2007 as well. FWIW, Excel 2007 supports far more columns than previous
versions,

--
Cheers
macropod
[MVP - Microsoft Word]

Hello, I'm using Word 2007 trying to create a catalog-style merge, using an
Excel 2007 file as the data source. When I set it up, I get a checkbox that
allows me to specify if the 1st row contains column headers, y or n, which
works fine if my source Excel file is set up that way. But if it is, I run
out of columns.. Excel seems to allow only up to column 'IV.' so I want to
"transpose" my data, in Excel speak, to have headers specified in the first
COLUMN of Excel, and the records (which make up the eventual catalog1.doc,
catalog2.doc, etc resultant files) would be found in the entirety of columns
B, then column C, etc. So I'm trying to take advantage of the many more rows
than columns that Excel offers, which would solve the problem in my case. Can
anyone offer suggestions as to how I can tackle this?

Very much appreciated!

 

[Peter Jamieson]

Just to be clear, you can have more than 255 fields in a Mailmerge - just
not using Excel (and lots of other things) as a data source.

Personally, I would probably do something along those lines too, although
I'd probably be more inclined to copy the worksheet (especially if I needed
to use DDE to connect, in which case the data has to be in the first sheet
in the book) then manipulate the copy. However, for many people,
copying/pasting into Word may be an simpler option than that - it's
difficult to tell, because everyone has their own way of working.


--
Peter Jamieson
http://tips.pjmsn.me.uk

Hi Peter,

Given that you can't have more than 255 fields in a mailmerge, the ability
to have more than 256 columns in a worksheet isn't much use. If my data
set had more than 256 columns, I'd probably want to insert a new worksheet
as the mailmerge source, linked to just the relevant columns in the data
set.

--
Cheers
macropod
[MVP - Microsoft Word]

Yes, unfortunately despite Excel's increased capacity, none of the
built-in methods that Word MailMerge has to get data from Excel lets you
see more than 255 (or maybe 256) columns - unless something has changed
recently.

One thing that may work is to create a new blank document in Word, then
in Excel, select the data, Edit->Copy (or Excel 2007 equivalent), then in
Word Paste->Special (click the arrow under the Paste button) and select,
say, Unformatted text or perhaps unformatted Unicode text. You should
then end up with the data in tab-delimited format within Word. Save that,
and use it as the data source for the merge.

It may not work if you have multiline data in Excel, and you may be
prompted for delimiter characters within Word, don't expect the Edit
Recipients dialog to work perfectly, etc.

--
Peter Jamieson
http://tips.pjmsn.me.uk

Hi Michael,

You mention Word 2007, so I guess you're using Excel 2007 as well. FWIW,
Excel 2007 supports far more columns than previous versions,

--
Cheers
macropod
[MVP - Microsoft Word]


message Hello, I'm using Word 2007 trying to create a catalog-style merge,
using an
Excel 2007 file as the data source. When I set it up, I get a checkbox
that
allows me to specify if the 1st row contains column headers, y or n,
which
works fine if my source Excel file is set up that way. But if it is, I
run
out of columns.. Excel seems to allow only up to column 'IV.' so I want
to
"transpose" my data, in Excel speak, to have headers specified in the
first
COLUMN of Excel, and the records (which make up the eventual
catalog1.doc,
catalog2.doc, etc resultant files) would be found in the entirety of
columns
B, then column C, etc. So I'm trying to take advantage of the many more
rows
than columns that Excel offers, which would solve the problem in my
case. Can
anyone offer suggestions as to how I can tackle this?

Very much appreciated!