Splitting up repeating data in a mail merge field

[Colin Richardson]

Hi All,

I'm passing repeating data to a mail merge in a single field, but the client
wants to carve up this data in a myriad of ways on different reports.

How would any-one suggest I do this?

The data is being passed in a well defined structure so the programming
isn't hard ... but I can't find any doco on string manipulation with the SET
function.

All help welcome!

Thanks

Colin Richardson

 

[Peter Jamieson]

The data is being passed in a well defined structure so the programming

isn't hard ... but I can't find any doco on string manipulation with the SET
function.

Unfortunately, this is because there are no string manipulation functions in
Word's "field language". There are only really numeric functions provided by
the { = } field. Further, because Word is not really very good at merging
handling repeating groups in merges, even splitting up the data before it
reaches Word is probably not going to make provision of a useful toolkit to
your client much easier. However, I would suggest that if the client wants
to do this...

wants to carve up this data in a myriad of ways on different reports.

....then Word's built-in mailmerge is probably not a very good basis for a
toolkit. I suspect the only way you'll be able to create a /useful/ toolkit
is to "roll your own" merge using VBA to populate placeholder fields of your
own invention, and provide that facility to the client as a basis/sample to
allow them to develop other applications, but of course that requires the
client to have their own expertise in developing and modifying VBA.

 

[Cindy Meister -WordMVP-]

Hi Colin,

Please provide more detail on what you want to do. Are you trying to "parse"
strings out of a single merge field's data? Word has no functionality in
fields for manipulating strings. This would have to be done in the data
source, or in the SQL linking in the data (assuming the data provider supports
the commands you need, and the entire SQL doesn't exceed 255 characters)

I'm passing repeating data to a mail merge in a single field, but the client
wants to carve up this data in a myriad of ways on different reports.

How would any-one suggest I do this?

The data is being passed in a well defined structure so the programming
isn't hard ... but I can't find any doco on string manipulation with the SET
function.

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Jan 24 2003)
http://www.mvps.org/word

This reply is posted in the Newsgroup; please post any follow question or
reply in the newsgroup and not by e-mail

 

[Colin Richardson]

Hi Cindy,

The full scenario is that I'm actually accessing the Word Mail Merge via the
ActiveX controls.

The program that I've written reads a record from a SQL Server DB, does a
bit of manipulation (labels, string formatting, etc) puts the data in a CSV
file, then I instantiate the word object, link in the CSV and trigger the
mail merge.

(this all came about because the client already has a heap of mail merge
templates that they won't to re-use)

So here I am, constructed this repeating data (e.g. Employment History) into
a nicely formatted block and the client wants it *sometimes* to be formatted
differently.

e.g. the format of the data I'm passing may be

"Date: 12/2000 - 1/2003
Employer: Microsoft
Role: Software Engineer

Date: 1/1996 - 12/2000
Employer: Hewlett-Packard
Role: Data Architect"

But in some cases they might just want the employer - so in this example I'd
like to be able to write a VBA function/macro which takes the 2nd and 6th
lines and returns those.

I know I could alter the data that is being passed, but to date I've been
able to make the CSV file nice and generic so I don't have to re-code it
every five minutes - so if there was some way I could create a field linked
to some VBA code, then that'd be fantastic.

.....still hoping,

Colin

 

[Colin Richardson]

Oh...and this is in Word 2000 or Word XP

Hi Colin,

Please provide more detail on what you want to do. Are you trying to "parse"
strings out of a single merge field's data? Word has no functionality in
fields for manipulating strings. This would have to be done in the data
source, or in the SQL linking in the data (assuming the data provider supports
the commands you need, and the entire SQL doesn't exceed 255 characters)


Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Jan 24 2003)
http://www.mvps.org/word

This reply is posted in the Newsgroup; please post any follow question or
reply in the newsgroup and not by e-mail

 

[Cindy Meister -WordMVP-]

Hi Colin,

Actually removing the fields coming in from the data source (changing the
QueryString property) would lead to error messages if the fields are still
left in the document. And I think it would be just too much work to take
fields out, and put them back in if the user changes his mind.

Thinking through various scenarios, I think the best approach might be to
format every "piece" of information with a particular style. When the user
says, "I don't want this field", you change the style definition to use hidden
font.

But in some cases they might just want the employer - so in this example I'd
like to be able to write a VBA function/macro which takes the 2nd and 6th
lines and returns those.

I know I could alter the data that is being passed, but to date I've been
able to make the CSV file nice and generic so I don't have to re-code it
every five minutes - so if there was some way I could create a field linked
to some VBA code, then that'd be fantastic.

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Jan 24 2003)
http://www.mvps.org/word

This reply is posted in the Newsgroup; please post any follow question or
reply in the newsgroup and not by e-mail

 

[Colin Richardson]

Well it's not really removing any fields from the data source, but
manipilating a single mail merge field...

I guess the anwer at the end of the day is you can't manipulate a mail merge
field (except via Styles which are pretty limited)

 

[Cindy Meister -WordMVP-]

Hi Colin,

I guess the anwer at the end of the day is you can't manipulate a mail merge
field (except via Styles which are pretty limited)

It really depends on what you want to do. You haven't been terribly specific
about what you have in mind. You can basically do with merge fields what you
can do with anything else in a Word document... But since this is Word, you
are bound to those limitations.

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Jan 24 2003)
http://www.mvps.org/word

This reply is posted in the Newsgroup; please post any follow question or
reply in the newsgroup and not by e-mail