word - replacing character with new line? 2

[RussellDonders]

Hi,

I have a data source with contact details in it with fields such as name, phone, address etc. This is from an MS SQL database that is used by another legacy application and structure can not be changed.

Problem is the address field is complex in that it is stored all on one line seperated by comma ','.

What I need to do to create mailing labels and letters is to split the address to a new line everytime there is a comma, so the comma is a delimter for the new line.

I was looking at switches but couldn't see how to get it out. can someone point me in the right direction ?

Much appreciated.

Russell

 

[fumei]

You most definitely do not want to do a document wide replace of "," with a Chr(13) as that would make every comma a new line. Possible, but it is not likely there are no legitimate commas.

I am no mail merge expert, but if there is a way of identifying the field (by name?, by index number?), then:

1. take the contents out (it is simply a string);
2. use normal string manipulate functions (Left, Right, Mid etc., but certainly InStr to find that "," )to make a new string;
3. either replace the field itself with the new string, OR replace the contents of the field with the new string.

Unless there is some ability withion mail merges to do, essentially, post merge value manipulation, then it will require VBA. However, again, if you can identify the field, then it should be fairly easy VBA.

Gerry

 

[RussellDonders]

thanks Gerry -

I was hoping to keep it simple as it seems like it would be quite a common task.

I have never used VBA inside word - i am a capable programmer and I'm sure i'll be able to get the code out once i get started.

Could someone point me in the right direction of where i would write the code, and how i would trigger it from inside word.

This is something that will need to be used on a daily basis by our non-technical secreterial staff (to print letters/labels).

Russell

 

[TonyJollans]

Hi Russell,

The format of the underlying data might not be changeable but the whole point (well, maybe I exaggerate, but a good point) of databases is that the user is isolated from the underlying data structures.

Can you not get a Query or a View (sorry, not sure of the terminology in what I presume you mean is SQL Server) set up - and if you can't, what about setting up your own front end in Access to use as your Mail Merge source.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[