[Nit_Wit_400]

Hello all.

Is there any way to set up my mail merge fields to stay whenever I
merge from a database.

For instance, I have multiple databases which are all set up the same
way, and I need to merge them into one word document with the same
repeating format.

For example:

((DB1)) Nottle: 36,375.0-125.J-00-000 and 15.23 acres, abstract no.
0511092101-02;
((DB2)) Nottle: 36,375.0-125.C-00-000 and 15.24 acres, abstract no.
0511092101-06;
((DB3)) Hons: 36,375.0-125.A-00-000 and 1.09 acres, abstract no.
0511092101-07;
((DB4)) Herman: 36,375.0-125.B-00-000 and 7.30 acres, abstract no.
0511092101-01;
((DB5)) DiGian: 36,375.0-125.F-00-000 and 17.17 acres, abstract no.
0511092101-04;
((DB6)) DiGian: 36,375.0-125-E-00-000 and 17.70 acres, abstract no.
0511092101-03;
So on and so forth.
The fields being Last Name: Number and Number acres,
abstract no. Number;

If there is any way to make those fields repeat like that without
having to copy-paste them, or whatever, please help me out?

[Nit_Wit_400]

Sorry; forgot to mention I'm merging into Word 2003 and Excel 2003 is
my database.

[Peter Jamieson]

Are your separate databases in separate workbooks, or are they in the
same workbook but in different worksheets?

Does each database have the same column names and sequence of columns
/in the data source/ ?

What sequence of records do you need in your output document (i.e. do
you need the first record from each data source, followed by the second
record from each data source, etc. When one data source runs out of
records, do you any kind of placeholder or do you just want to continue
with the next available record?

(FWIW the simplest approach is likely to be to use Excel formulas or
Excel VBA code to gather all the records into a single worksheet).

Peter Jamieson

http://tips.pjmsn.me.uk
Visit Londinium at http://www.ralphwatson.tv

Nit_Wit_400 wrote:
Sorry; forgot to mention I'm merging into Word 2003 and Excel 2003 is
my database.

[Nit_Wit_400]

Mr. Jamieson,

Are your separate databases in separate workbooks, or are they in the
same workbook but in different worksheets?

Yes, my data is in separate excel spreadsheets.

Does each database have the same column names and sequence of columns
/in the data source/ ?

Yes, each file is exactly the same, except for the information under
the headers. In actuality, I have a header row for which each of that
row's cell's column contains information from the spreadsheet. For
example:
Row 1 = "1"....."98"
Row 2 = B8....B98
Col A = Header column for col B

It is done this way so that the word document says F1 instead of
Client Name .. can't say I like it, but that's the way chain of
command works around here. I'm told to do something and I have to do
it whether I like the way it is done or not .

What sequence of records do you need in your output document (i.e. do
you need the first record from each data source, followed by the second
record from each data source, etc. When one data source runs out of
records, do you any kind of placeholder or do you just want to continue
with the next available record?

Each spreadsheet has the data for each client, however, in the Word
document, we need to combine the information into one. Ideally, it
needs to handle X amount of input and it needs to be in the format
from my last posting. I'm not sure what you mean by my data source
running out of records because the mergefield wouldn't be there if
there wasn't going to be information to put in it, but I guess if for
some reason the cell was erroneously empty, an error message should
appear. However, I guess in some cases, there would be an instance
when there was no information given to us about the cell's required
information. In this case, it should either be skipped, or if it is
purposely empty, it usually means that a whole section of the word
document should be eliminated.

[Peter Jamieson]

You'll need a macro in this case - I suggest you look at

http://msdn.microsoft.com/en-us/library/cc793964.aspx

which has some examples with instructions on how to adapt the code for
various situations. If you need help with that, post back. However, if
you need to do that I could use some clarification about the format of
your worksheets.

I'm told to do something and I have to do
it whether I like the way it is done or not .

I'm familiar with that set-up.

Yes, each file is exactly the same, except for the information under
the headers. In actuality, I have a header row for which each of that
row's cell's column contains information from the spreadsheet. For
example:
Row 1 = "1"....."98"
Row 2 = B8....B98
Col A = Header column for col B

I didn't really understand the details you have here and wonder if there
is a typo. Perhaps an example that shows what the first 5 rows of one of
the sheets looks like would help.

I'm not sure what you mean by my data source
running out of records

What I meant was this - your example of the output shows 6 records, each
one from a different workbook. To simplify, suppose you have 3 workbooks
A,B, and C with 8, 6 and 4 records respectively. If "A1" means the first
row of data in workbook A, should the output be in this sequence:

A1
B1
C1
A2
B2
C2
A3
B3
C3
A4
B4
C4
A5
B5
A6
B6
A7
A8

or what (it could be for example that you needs blocks of three like this

A1
B1
C1

A2
B2
C2

A3
B3
C3

A4
B4
C4

A5
B5
empty row

A6
B6
empty row

A7
empty row
empty row

A8
empty row
empty row

)


Peter Jamieson

http://tips.pjmsn.me.uk
Visit Londinium at http://www.ralphwatson.tv

Nit_Wit_400 wrote:
Mr. Jamieson,

Are your separate databases in separate workbooks, or are they in the
same workbook but in different worksheets?

Yes, my data is in separate excel spreadsheets.

Does each database have the same column names and sequence of columns
/in the data source/ ?

Yes, each file is exactly the same, except for the information under
the headers. In actuality, I have a header row for which each of that
row's cell's column contains information from the spreadsheet. For
example:
Row 1 = "1"....."98"
Row 2 = B8....B98
Col A = Header column for col B

It is done this way so that the word document says F1 instead of
Client Name .. can't say I like it, but that's the way chain of
command works around here. I'm told to do something and I have to do
it whether I like the way it is done or not .

What sequence of records do you need in your output document (i.e. do
you need the first record from each data source, followed by the second
record from each data source, etc. When one data source runs out of
records, do you any kind of placeholder or do you just want to continue
with the next available record?

Each spreadsheet has the data for each client, however, in the Word
document, we need to combine the information into one. Ideally, it
needs to handle X amount of input and it needs to be in the format
from my last posting. I'm not sure what you mean by my data source
running out of records because the mergefield wouldn't be there if
there wasn't going to be information to put in it, but I guess if for
some reason the cell was erroneously empty, an error message should
appear. However, I guess in some cases, there would be an instance
when there was no information given to us about the cell's required
information. In this case, it should either be skipped, or if it is
purposely empty, it usually means that a whole section of the word
document should be eliminated.

[Nit_Wit_400]

Mr. Jamieson

Sorry for taking so long to reply; I got caught up in other work
stuff. But alas, I'm back to this particular grindstone.

Actually, I've made some headway.

I've figured out an easy-ish way which will theoretically solve the
issue. It will still require some grunt-work, but it will cut out
some serious time in the end-product.

So, in my workbook, I have a header row which refers to the
information in column B:
Row 1 is numbered 1-99
Row 2 =B8:B106 meaning A2 thru CU2 is equal to B8 thru B106 -- know
what I mean?
Row 3 has information for the dropdown menus throughout the
spreadsheet. This is done to prevent erroneous entries in some
specific cells -- some of our analysts are daft.
For some unholy reason, A4:6 thru C4:6 are merged together to make the
title cell of the spreadsheet (don't know why whoever made the
template just didn't use one cell... but whatever, that's the way it
is). The first 3 rows are hidden so that the analyst can't tamper
with the spreadsheet.
Anyway, row 7 is blank (to separate the meat from the title).

The rest of Column A refers to what the analyst should put in the
corresponding cells of column B.
--------------above here is what is currently used as a mailmerge data
source

And until now, this worked fine, as long as the Word document only
needed one set of information.
Recent developments require that the Word document contains up to (and
hopefully no more than) 10 sets.

So, my idea is to make 9 more header rows which refer to columns C
thru J if you catch my drift.

Think that'll work?

[macropod[_2_]]

Hi Nit_Wit_400,

A Word mailmerge main document can have only one data source, so what you want to do isn't possible in the way you want to do it.
You could try running a db query to extract the data from each database into a single table that Word could use as its mailmerge
datasource.

--
Cheers
macropod
[Microsoft MVP - Word]


"Nit_Wit_400" wrote in message ...
Hello all.

Is there any way to set up my mail merge fields to stay whenever I
merge from a database.

For instance, I have multiple databases which are all set up the same
way, and I need to merge them into one word document with the same
repeating format.

For example:

((DB1)) Nottle: 36,375.0-125.J-00-000 and 15.23 acres, abstract no.
0511092101-02;
((DB2)) Nottle: 36,375.0-125.C-00-000 and 15.24 acres, abstract no.
0511092101-06;
((DB3)) Hons: 36,375.0-125.A-00-000 and 1.09 acres, abstract no.
0511092101-07;
((DB4)) Herman: 36,375.0-125.B-00-000 and 7.30 acres, abstract no.
0511092101-01;
((DB5)) DiGian: 36,375.0-125.F-00-000 and 17.17 acres, abstract no.
0511092101-04;
((DB6)) DiGian: 36,375.0-125-E-00-000 and 17.70 acres, abstract no.
0511092101-03;
So on and so forth.
The fields being Last Name: Number and Number acres,
abstract no. Number;

If there is any way to make those fields repeat like that without
having to copy-paste them, or whatever, please help me out?