Mail merge - format as currency or as text

[dirtychinch]

Morning guys,

I have a mail merge that comes in from Excel, where one value can either be a price eg £400.00, or can be a statement, which is "TBA (£515 per day)".

Currently, the formatting on the field is "\#£,#.00" which works perfectly for currency, but if the merge text is "TBA (£515 per day)" the outcome of the mail merge just gives £515.00, which is very clever, but not what I'm after.

Does anyone know how I could get around this? I've tried various If statements within the fields, but to no avail. That text will always be the same, by the way.

Thanks in advance,

dirtychinchilla

[dirtychinch]

I have an alternative idea, which is for Word to preserve the original formatting. This isn't ideal, but could work. However, I'm not sure how to include that in my VBA code.

[macropod]

You can use a field coded as:
{QUOTE{SET Val {MERGEFIELD Data}}{IF{Val}= {=Val} {=Val \# £,0.00} {REF Val}}}

Note: The field brace pairs (ie '{ }') for the above example are created in the document itself, via Ctrl-F9 (Cmd-F9 on a Mac); you can't simply type them or copy & paste them from this message. Nor is it practicable to add them via any of the standard Word dialogues.

[dirtychinch]

Hi macropod,

Thank you very much for that. I understand your instructions with regards to how to enter the field code. I ended up with this:

{QUOTE{SET R3C3 {MERGEFIELD Data}}{IF{R3C3}= {=R3C3} {=R3C3 \# £,0.00} {REF R3C3}}}

But it seems only to give a blank result now...any ideas??

I'll keep trying to fix it.

dirtychinchilla

[macropod]

Works fine for me, though using 'R3C3' is unwise due to the risk of it being interpreted as a cell address, but it's actually a bookmark name in this context.

Are you finishing the merge, or just previewing it? Previews do not give a reliable indicator of the output with anything other than fairly simple fields.

[dirtychinch]

Really? I can't get anything other than blank, no matter what values (text or currency) I'm putting in.

The R3C3 does refer to the obvious, but it seemed the best way to name things coming from Excel.

I tried finishing the merge and it remained blank...

[macropod]

Are you creating all the field braces via Ctrl-F9 as stated in post #3, and is 'Data' your datasource record's field name?

[dirtychinch]

I'm doing the Alt+F9 bit, but I don't understand what you mean by "your datasource record's field name"

[macropod]

I'm doing the Alt+F9 bit, but I don't understand what you mean by "your datasource record's field name"

The procedure refers to Ctrl-F9, not Alt-F9, which won't do the same thing.
As for the field name, in your first post you said:

I have a mail merge that comes in from Excel, where one value can either be a price eg £400.00, or can be a statement, which is "TBA (£515 per day)".

You should use whatever the name of that field is instead of 'Data' in the field code.

[dirtychinch]

Oh OK, I understand. I had managed to get them into the grey area within the {} brackets already.

Have I incorrectly replaced Val with R3C3, R3C3 being my field name??

[dirtychinch]

Leaving Val as Val does nothing for me, even when data is as R3C3...

[macropod]

You are not paying attention. The only thing I've said you should change from the original field code is Data. Change Data to whatever your mergefield name is, not its cell address or something else, the name (i.e. what you see when you insert the field via Insert|Mergefield).

One change that should be made, is to add another REF to the field code, so as to ensure the '£' is applied correctly. Instead of:
{QUOTE{SET Val {MERGEFIELD Data}}{IF{Val}= {=Val} {=Val \# £,0.00} {REF Val}}}
use:
{QUOTE{SET Val {MERGEFIELD Data}}{IF{REF Val}= {=Val} {=Val \# £,0.00} {REF Val}}}

[dirtychinch]

Hi macropod,

Sorry it's taken me so long to reply - I had Friday off work!

I changed the field code to exactly what you said, with data changed for R3C3 (my mergefield name). Unfortunately, this does nothing. And, oddly, it doesn't seem to recognise itself as being a field to some extent. The field shows up when you toggle field codes, but is gone when you toggle them again returning to the default view.

Here are some pictures for reference:

First stage



Second Stage



Third Stage

[macropod]

Since you don't say what your 'stages' are, I don't know what I'm supposed to make of the pictures. I must say, though, that I find the idea of having cell addresses as mergefield names, as you seem to have, strange, to say the least.

You would do far better to post a copy of both your mailmerge main document and a representative sub-set of your data source (remove anything sensitive). You do this via the paperclip symbol on the 'Go Advanced' tab at the bottom of this screen.

[dirtychinch]

Macropod,

The stages are as they appear. The first stage is entering the field code into the field. The second stage is when I toggle the field codes off (and you can see that this code we're discussing has disappeared). The third stage is what happens when I actually do the mail merge.

I have attached the Excel document from which the data transfers, as well as the word document in which the merge occurs. You will have to amend the file paths where, in the code, you have " Set oMailMergeDoc = appWord.Documents.Add("Q:\HIU\Documents\CHP Quotation.dotm")"

CHP Clean.xlsm

Thanks,

dirtychinchilla

[dirtychinch]

I can only attached one item per post, so the word document is attached to this one.

Quotation.docx

[macropod]

Please re-read the Note in post #3.

In the document you've posted, only the outer set of braces are true field braces. The other 7 pairs are just ordinary braces. The note clearly states "you can't simply type them or copy & paste them from this message", but that's all you've done...

[dirtychinch]

macropod, you're a genuis. I had done as you said in post #3, but it didn't actually work the first time. I must have made a mistake when typing it out.

Thank you very much for your help, and for your patience.

[macropod]

I had done as you said in post #3, but it didn't actually work the first time.

If you had followed the directions and input the field codes exactly as specified, it would have worked the first time.

I must have made a mistake when typing it out.

I suspect that's exactly what you did wrong - typing the braces...

[dirtychinch]

I didn't actually type the braces. I did as you said, although overwrote this at some point. Whenever I did it, it didn't work. It's the typing out of all the quote, ref, etc - easy to go wrong!