Mail merge more than 240 columns

[calebfda]

have an excel sheet i am using for a mail merge. unfortunatly i have
run out of columns....what is a solution. i am using al 240 columns
in the documnet.

 

[Billy Liddel]

You can extend the number of columns in Ex2007, but how have you set up the
merge?

Normally, the list includes, Title, FirstName, LastName, [Company],
Address1, Address2, Town, County, PostCode, [Country]. Where the column
headings in square brackets are optional. The document is produced in say,
Word and linked to the Excel file for merging.

If you want different paragraphs for say different groups (see your other
post), then you can have different paragraphs based on the groups set up in
Word. I can't remember how its done but the Word group is sure to help.

Can you reduce the number of columns (fields) in the worksheet or have I
missed the point?

HTH

Peter Atherton

 

[calebfda]

Peter

Basically each state has 5 contracts. I have set up the excel
spreadsheet to run calculations and record information. Variances,
highs/lows, changes, etc. So everything that i do is multiplied by
5. meaning i have reached the 250 mark in columns quickly. I then
set up a word document...not for mailing...so that i can populate a
document for each state with the figures and analyses...etc.
The only way i can think of getting around it is split one of the
contracts onto a different sheet....but this means i would have 2 word
documents for each state.

Any ideas?

You can extend the number of columns in Ex2007, but how have you set up the
merge?

Normally, the list includes, Title, FirstName, LastName, [Company],
Address1, Address2, Town, County, PostCode, [Country]. Where the column
headings in square brackets are optional. The document is produced in say,
Word and linked to the Excel file for merging.

If you want different paragraphs for say different groups (see your other
post), then you can have different paragraphs based on the groups set up in
Word. I can't remember how its done but the Word group is sure to help.

Can you reduce the number of columns (fields) in the worksheet or have I
missed the point?

HTH

Peter Atherton

have an excel sheet i am using for a mail merge. unfortunatly i have
run out of columns....what is a solution.  i am using al 240 columns
in the documnet.- Hide quoted text -

- Show quoted text -

 

[Billy Liddel]

MM my ISP connection went down in the middle of replying. Two things come to
mind.
First, I'd have a separate row for each contract so that each state will
have 5 rows.
2nd. Have you tried a Pivot Table (PT) to sumarise the data? They are pretty
flexible and you can get data from more than one sheet but this seems to
limit the contol you have.

I would try splitting the data then using a PT to produce a report before
trying anything else. Debra Dalgliesh has two books on PTs, niethr of which
I've got yet but certainly will in the future.

Regards
Peter

Peter

Basically each state has 5 contracts. I have set up the excel
spreadsheet to run calculations and record information. Variances,
highs/lows, changes, etc. So everything that i do is multiplied by
5. meaning i have reached the 250 mark in columns quickly. I then
set up a word document...not for mailing...so that i can populate a
document for each state with the figures and analyses...etc.
The only way i can think of getting around it is split one of the
contracts onto a different sheet....but this means i would have 2 word
documents for each state.

Any ideas?

You can extend the number of columns in Ex2007, but how have you set up the
merge?

Normally, the list includes, Title, FirstName, LastName, [Company],
Address1, Address2, Town, County, PostCode, [Country]. Where the column
headings in square brackets are optional. The document is produced in say,
Word and linked to the Excel file for merging.

If you want different paragraphs for say different groups (see your other
post), then you can have different paragraphs based on the groups set up in
Word. I can't remember how its done but the Word group is sure to help.

Can you reduce the number of columns (fields) in the worksheet or have I
missed the point?

HTH

Peter Atherton

have an excel sheet i am using for a mail merge. unfortunatly i have
run out of columns....what is a solution. i am using al 240 columns
in the documnet.- Hide quoted text -

- Show quoted text -

 

[macropod]

Hi Caleb,

A mailmerge using Word can handle at least 255 columns. That's how many the mailmerge wizard will show. If you've got more columns
than that, the mailmerge wizard won't show the extras but, if you know the field names, you *may* still be able to insert them into
the document manually. To do this for a field in your Excel workbook named 'My Field':
.. press Ctrl-F9 to create a pair of field braces (ie '{}')
.. between the field braces, type 'MERGEFIELD My_Field' so that you get '{MERGEFIELD My_Field}' (note the underscore that's needed
where there's a space in the data field's name)
.. run your mailmerge.

An alternative approach, where you've got multiple entries for the same State, might be to use Word's Catalogue/Directory Mailmerge
facility (the terminolgy depends on the Word version). To see how, check out my Word 97-2007 Catalogue/Directory Mailmerge Tutorial
at:
http://www.wopr.com/index.php?showtopic=731107
or
http://www.gmayor.com/Zips/Catalogue Mailmerge.zip
Do read the tutorial before trying to use the mailmerge document included with it.

--
Cheers
macropod
[Microsoft MVP - Word]


Peter

Basically each state has 5 contracts. I have set up the excel
spreadsheet to run calculations and record information. Variances,
highs/lows, changes, etc. So everything that i do is multiplied by
5. meaning i have reached the 250 mark in columns quickly. I then
set up a word document...not for mailing...so that i can populate a
document for each state with the figures and analyses...etc.
The only way i can think of getting around it is split one of the
contracts onto a different sheet....but this means i would have 2 word
documents for each state.

Any ideas?

You can extend the number of columns in Ex2007, but how have you set up the
merge?

Normally, the list includes, Title, FirstName, LastName, [Company],
Address1, Address2, Town, County, PostCode, [Country]. Where the column
headings in square brackets are optional. The document is produced in say,
Word and linked to the Excel file for merging.

If you want different paragraphs for say different groups (see your other
post), then you can have different paragraphs based on the groups set up in
Word. I can't remember how its done but the Word group is sure to help.

Can you reduce the number of columns (fields) in the worksheet or have I
missed the point?

HTH

Peter Atherton

have an excel sheet i am using for a mail merge. unfortunatly i have
run out of columns....what is a solution. i am using al 240 columns
in the documnet.- Hide quoted text -

- Show quoted text -

 

[calebfda]

Macropod

my issue lies in excel....which has limited the number of columns i
can have.....i have run out of spreadsheet. is there a way that word
will recognize row headings for a mail merge (if i was to do as peter
suggested above and make states by column headings)?

Hi Caleb,

A mailmerge using Word can handle at least 255 columns. That's how many the mailmerge wizard will show. If you've got more columns
than that, the mailmerge wizard won't show the extras but, if you know the field names, you *may* still be able to insert them into
the document manually. To do this for a field in your Excel workbook named 'My Field':
. press Ctrl-F9 to create a pair of field braces (ie '{}')
. between the field braces, type 'MERGEFIELD My_Field' so that you get '{MERGEFIELD My_Field}' (note the underscore that's needed
where there's a space in the data field's name)
. run your mailmerge.

An alternative approach, where you've got multiple entries for the same State, might be to use Word's Catalogue/Directory Mailmerge
facility (the terminolgy depends on the Word version). To see how, check out my Word 97-2007 Catalogue/Directory Mailmerge Tutorial
at:http://www.wopr.com/index.php?showtopic=731107
orhttp://www.gmayor.com/Zips/Catalogue%20Mailmerge.zip
Do read the tutorial before trying to use the mailmerge document includedwith it.

--
Cheers
macropod
[Microsoft MVP - Word]


Peter

Basically each state has 5 contracts.  I have set up the excel
spreadsheet to run calculations and record information.  Variances,
highs/lows, changes, etc.  So everything that i do is multiplied by
5.  meaning i have reached the 250 mark in columns quickly.  I then
set up a word document...not for mailing...so that i can populate a
document for each state with the figures and analyses...etc.
The only way i can think of getting around it is split one of the
contracts onto a different sheet....but this means i would have 2 word
documents for each state.

Any ideas?

You can extend the number of columns in Ex2007, but how have you set upthe
merge?

Normally, the list includes, Title, FirstName, LastName, [Company],
Address1, Address2, Town, County, PostCode, [Country]. Where the column
headings in square brackets are optional. The document is produced in say,
Word and linked to the Excel file for merging.

If you want different paragraphs for say different groups (see your other
post), then you can have different paragraphs based on the groups set up in
Word. I can't remember how its done but the Word group is sure to help.

Can you reduce the number of columns (fields) in the worksheet or have I
missed the point?

Peter Atherton

- Show quoted text -- Hide quoted text -

- Show quoted text -

 

[macropod]

Hi Caleb,

Row headings will not work with a mailmerge - the data must be on a single worksheet organised by columns, with column headings.

If you moved to Excel 2007, you could have more than 255 columns. However, it would probably be better to organise you data by rows,
so that all State Names are in the same column and the contract data for each State are either on the same row or on a consecutive
series of rows (ie 1 row per contract). Even with Excel 5 that'd give you 16,384 rows to play with - Excel 97-2003 have 65,536 rows.

With the data set up as one row per State, a simple 'letter' mailmerge would probably achieve what you're after. With the data set
up as multiple rows per State, a catalog/directory mailmerge (per my tutorial) would probably achieve what you're after.

An alternative approach, which can work regardless of your workbook layout and with multiple worksheets, is to insert links between
Word and Excel. You can set up such a link by copying an Excel cell, switching to Word and using Edit|Paste Special, choosing the
'Link' option and a suitable paste format. This works well if the cells you want to update from will always be in the same locations
in the Excel workbook (ie you aren't going to be adding/deleting rows/columns).


--
Cheers
macropod
[Microsoft MVP - Word]


Macropod

my issue lies in excel....which has limited the number of columns i
can have.....i have run out of spreadsheet. is there a way that word
will recognize row headings for a mail merge (if i was to do as peter
suggested above and make states by column headings)?

Hi Caleb,

A mailmerge using Word can handle at least 255 columns. That's how many the mailmerge wizard will show. If you've got more columns
than that, the mailmerge wizard won't show the extras but, if you know the field names, you *may* still be able to insert them
into
the document manually. To do this for a field in your Excel workbook named 'My Field':
. press Ctrl-F9 to create a pair of field braces (ie '{}')
. between the field braces, type 'MERGEFIELD My_Field' so that you get '{MERGEFIELD My_Field}' (note the underscore that's needed
where there's a space in the data field's name)
. run your mailmerge.

An alternative approach, where you've got multiple entries for the same State, might be to use Word's Catalogue/Directory
Mailmerge
facility (the terminolgy depends on the Word version). To see how, check out my Word 97-2007 Catalogue/Directory Mailmerge
Tutorial
at:http://www.wopr.com/index.php?showtopic=731107
orhttp://www.gmayor.com/Zips/Catalogue%20Mailmerge.zip
Do read the tutorial before trying to use the mailmerge document included with it.

--
Cheers
macropod
[Microsoft MVP - Word]


Peter

Basically each state has 5 contracts. I have set up the excel
spreadsheet to run calculations and record information. Variances,
highs/lows, changes, etc. So everything that i do is multiplied by
5. meaning i have reached the 250 mark in columns quickly. I then
set up a word document...not for mailing...so that i can populate a
document for each state with the figures and analyses...etc.
The only way i can think of getting around it is split one of the
contracts onto a different sheet....but this means i would have 2 word
documents for each state.

Any ideas?

You can extend the number of columns in Ex2007, but how have you set up the
merge?

Normally, the list includes, Title, FirstName, LastName, [Company],
Address1, Address2, Town, County, PostCode, [Country]. Where the column
headings in square brackets are optional. The document is produced in say,
Word and linked to the Excel file for merging.

If you want different paragraphs for say different groups (see your other
post), then you can have different paragraphs based on the groups set up in
Word. I can't remember how its done but the Word group is sure to help.

Can you reduce the number of columns (fields) in the worksheet or have I
missed the point?

Peter Atherton

- Show quoted text -- Hide quoted text -

- Show quoted text -