[jpf5911]

I can't believe something as obvious as retaining the formats from the Excel sheet to the merged Word document isn't part of the programming for these applications!!

I have read all the help on this topic and tried to use the stupid formatting switches in the field codes to no avail.

I have a numeric field that I want formatted as a dollar field. I have used "alt f9" to go into the field code and edited as follows:

{MERGEFIELD"Orig_Prin" \ # $#,##0.00}

"Orig_Prin" is a field name from my Excel file. In Excel, the data is formated to appear "$6,000.00", but no matter what I try, it shows up in the merged doc as "6000".

This makes me so mad, I have spent so much time on it I could have typed all the letters on a manual typewriter by now!! Please help.

Thanks,

JPF

[Advertisements]

Hi jpf5911

First of all, have a look at my reply in this topic.

http://www.geekstogo...1&hl=mergefield

Two things, make sure that you have the neccessary Office updates and note the format for Mergefield highlighted in blue.

Please post back with your results.

"O"

[Octagonal]

Hi jpf5911

First of all, have a look at my reply in this topic.

http://www.geekstogo...1&hl=mergefield

Two things, make sure that you have the neccessary Office updates and note the format for Mergefield highlighted in blue.

Please post back with your results.

"O"

[jpf5911]

Ok "O", I'm going to try again. How can I tell if I have "SP3"?

BTW, in your earlier post where you show the example for the workaround, can you clarify where (if any) there are spaces in the stuff between the brackets?

Thanks,

JPF

Edited by jpf5911, 05 July 2005 - 04:52 PM.

[Octagonal]

Sorry about the delay replying.

To check if you have Office SP3 installed check this link, and read the how to determine if the update is installed section. If you haven't got the updated files, make sure that you download and install the "OfficeXpSp3-kb832671-fullfile-enu.exe" file option.

I assume that you are using the Mail Merge Wizard in Word. If so, after you write your letter and insert the Fields from Excel, highlight and right click on the "Orig_Prin" field and select Edit Field... option. Click on the Field Codes button located at the bottom left of the dialog box that opens. In the Field Codes section type "MERGEFEILD Amount \# "$,0.00;($,0.00)" without the quotation marks. Click the OK button and continue with the merge. When closing the document make sure that you save changes.

Please post back with the results.

"O"

Edited by Octagonal, 06 July 2005 - 04:03 AM.

[jpf5911]

I did finally get it to work. Thanks for your help.



When you think about it, it is a very simple and intuitive way to get a comma and a decimal point in a number. The people at Microsoft who are responsible for this should be very proud of themselves . . .

JPF

[Octagonal]

Glad to be of help.

[dignityy]

Sorry about the delay replying.

To check if you have Office SP3 installed check this link, and read the how to determine if the update is installed section. If you haven't got the updated files, make sure that you download and install the "OfficeXpSp3-kb832671-fullfile-enu.exe" file option.

I assume that you are using the Mail Merge Wizard in Word. If so, after you write your letter and insert the Fields from Excel, highlight and right click on the "Orig_Prin" field and select Edit Field... option. Click on the Field Codes button located at the bottom left of the dialog box that opens. In the Field Codes section type "MERGEFEILD Amount \# "$,0.00;($,0.00)" without the quotation marks. Click the OK button and continue with the merge. When closing the document make sure that you save changes.

Please post back with the results.

"O"

This was a great help to me also, but there is one problem I am still having. After you go into the dialog box and edit the string as above and exit/save and the document runs with the formating as set up ... great... but when you go back into the edit dialog box the string you edited is automatically put back to its default setting just by going into the edit dialog box again. Why are the changes you made to the edit field not saved? Hope this made sense..
Thanks

[Octagonal]

I don't know. The settings should remain. I will have to look into a little further.

"O"

[dignityy]

I don't know. The settings should remain. I will have to look into a little further.

"O"

Thanks I'm looking forward to what you find out!

[Octagonal]

This was a great help to me also, but there is one problem I am still having.  After you go into the dialog box and edit the string as above and exit/save and the document runs with the formating as set up ... great... but when you go back into the edit dialog box the string you edited is automatically put back to its default setting just by going into the edit dialog box again.  Why are the changes you made to the edit field not saved?  Hope this made sense..
Thanks

This applies to Office XP(2002), I don't know about 2003. When you complete the merge with the above switches and then save and exit the document, the merged field properties appear to reset to the default (MERGEFIELD "Orig_Prin"). I believe that you will find that the field properties will retain the appropriate switches that you have set. To test this simply merge a document and format the field as outlined above then save and exit the document. Reopen the document and open the wizard again, but this time don't set any switches for the field. You will find that the field is formatted as you wish it to be, it doesn't lose the field formatting. I really don't know why, maybe you should just put it down to Uncle Bill. I use a similar merged document with the currency format as outlined above, and it works just fine every time that I do a merge.

Spooky, isn't it.

"O"

[dignityy]

This applies to Office XP(2002), I don't know about 2003. When you complete the merge with the above switches and then save and exit the document, the merged field properties appear to reset to the default (MERGEFIELD "Orig_Prin"). I believe that you will find that the field properties will retain the appropriate switches that you have set. To test this simply merge a document and format the field as outlined above then save and exit the document. Reopen the document and open the wizard again, but this time don't set any switches for the field. You will find that the field is formatted as you wish it to be, it doesn't lose the field formatting. I really don't know why, maybe you should just put it down to Uncle Bill. I use a similar merged document with the currency format as outlined above, and it works just fine every time that I do a merge.

Spooky, isn't it.

"O"

Yes Spooky, Thanks for the info!