[[email protected]]

Hello. I need to merge some data from MS SQL with word template.
There are many lines - some fields are unique, some not. I.e.
Table:
ID FirstName LastName Number Language
1 Mary Ann 1 Lang1
1 Mary Ann 1 Lang2
1 Mary Ann 1 Lang3
2 Peter Stas 1 Lang1
2 Peter Stas 1 Lang2
3 Bob Marley 1 Lang1

Document must be like:

Employee Mary Ann with number 1 knows
there is a table
--------------
Lang1 |
-------------
Lang2 |
-------------
Lang3 |
---------

And other docs with other people.

I've found article http://support.microsoft.com/kb/211303, but I can't
even make work correctly their example -
in doc was page breaks, city appears twice at first iteration... And I
can't insert here my code from document also.

Could anyone helps to solve that problem with complex merging?

[Peter Jamieson]

Although the data is rather different and the document is quite complex,
have a look at the following conversation:

http://groups.google.co.uk/group/mic...1c 46ceb8755e

The sample files are, I think still at

http://suzleigh.com/MERGE.aspx

However, inserting a Word /table/ in the way that you suggest cannot really
be done using this technique because when you mix fields and table rows,
there will always be gaps between table rows in the output, and they are not
particulalry easy to remove. There is another approach which uses a DATABASE
field with a "coordinated" query to insert the relevant rows (e.g.

{ DATABASE \d "whatever" \c "The conection info. for your database" \s
"SELECT * FROM myview WHERE myview.ID = '{ MERGEFIELD ID }' }

). But unfortunately
a. you have very limited control over the appearance of the output when you
do that.
b. You can't, for example, control the formatting of the data in each
column separately, and the table width may change from page to page
depending on the amount of space the output occupies.
c. using \*MERGEFORMAT in the DATABASE field to retain formatting that you
apply to the table might seem like a sensible thing to try, but IME it
breaks down quite rapidly, e.g. at a page break.

However, if you combined that approach with some VBA that either
postprocessed the output from the merge, or used Word's MailMerge events,
you might be able to achieve the formatting you need.

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

wrote in message
...
Hello. I need to merge some data from MS SQL with word template.
There are many lines - some fields are unique, some not. I.e.
Table:
ID FirstName LastName Number Language
1 Mary Ann 1 Lang1
1 Mary Ann 1 Lang2
1 Mary Ann 1 Lang3
2 Peter Stas 1 Lang1
2 Peter Stas 1 Lang2
3 Bob Marley 1 Lang1

Document must be like:

Employee Mary Ann with number 1 knows
there is a table
--------------
Lang1 |
-------------
Lang2 |
-------------
Lang3 |
---------

And other docs with other people.

I've found article http://support.microsoft.com/kb/211303, but I can't
even make work correctly their example -
in doc was page breaks, city appears twice at first iteration... And I
can't insert here my code from document also.

Could anyone helps to solve that problem with complex merging?

[[email protected]]

Good. I've decided to do it using VBA - it gives me full control over
the document with rich opportunities. Also, I need to do something
like this with excel templates...

Thank for ideas!

Peter Jamieson:
Although the data is rather different and the document is quite complex,
have a look at the following conversation:

http://groups.google.co.uk/group/mic...1c 46ceb8755e

The sample files are, I think still at

http://suzleigh.com/MERGE.aspx

However, inserting a Word /table/ in the way that you suggest cannot really
be done using this technique because when you mix fields and table rows,
there will always be gaps between table rows in the output, and they are not
particulalry easy to remove. There is another approach which uses a DATABASE
field with a "coordinated" query to insert the relevant rows (e.g.

{ DATABASE \d "whatever" \c "The conection info. for your database" \s
"SELECT * FROM myview WHERE myview.ID = '{ MERGEFIELD ID }' }

). But unfortunately
a. you have very limited control over the appearance of the output when you
do that.
b. You can't, for example, control the formatting of the data in each
column separately, and the table width may change from page to page
depending on the amount of space the output occupies.
c. using \*MERGEFORMAT in the DATABASE field to retain formatting that you
apply to the table might seem like a sensible thing to try, but IME it
breaks down quite rapidly, e.g. at a page break.

However, if you combined that approach with some VBA that either
postprocessed the output from the merge, or used Word's MailMerge events,
you might be able to achieve the formatting you need.

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