Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Excel cell-formatting lost when mail-merging under Office 2007
Hi, I've been given the task of fixing a Mail Merge that worked fine in
Office 2000 but loses the Excel cell-formatting when run under Office 2007. I understand this is due to the new Office using some form of ADO instead of DDE to transfer the data. So, I've been looking at Word formatting pages such as http://www.gmayor.com/formatting_word_fields.htm but Im still having a lot of trouble getting anything remotely complex to work in Word 2007. My need is to take a number such as 12345678000 from an Excel cell and format it in Word as £12,345.678m (ie, shown to 3 decimal places of millions). The original formatting string in Excel was £#,##0.000,,"m" (which seems to auto-divide by 1 million for display purposes as well as format the number) and I have tried to base my Word MERGEFIELD format string on this but with no success. I have so far only succeeded in getting the merged field to be formatted as £12,345,678,000 using \# "£#,##0" I have tried all sorts of formats and intermediate-value-formulae including SET ... and / 1000000 but nothing works. Can anyone help, please? |
#2
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Excel cell-formatting lost when mail-merging under Office 2007
You need a calculated field to divide by 1000000 i.e.
{={Mergefield Fieldname} / 1000000 \# "$,0.000m"} -- Graham Mayor - Word MVP My web site www.gmayor.com Word MVP web site http://word.mvps.org Ron West wrote: Hi, I've been given the task of fixing a Mail Merge that worked fine in Office 2000 but loses the Excel cell-formatting when run under Office 2007. I understand this is due to the new Office using some form of ADO instead of DDE to transfer the data. So, I've been looking at Word formatting pages such as http://www.gmayor.com/formatting_word_fields.htm but I'm still having a lot of trouble getting anything remotely complex to work in Word 2007. My need is to take a number such as 12345678000 from an Excel cell and format it in Word as 12,345.678m (ie, shown to 3 decimal places of millions). The original formatting string in Excel was #,##0.000,,"m" (which seems to auto-divide by 1 million for display purposes as well as format the number) and I have tried to base my Word MERGEFIELD format string on this but with no success. I have so far only succeeded in getting the merged field to be formatted as 12,345,678,000 using \# "#,##0" I have tried all sorts of formats and intermediate-value-formulae including "SET ..." and "/ 1000000" but nothing works. Can anyone help, please? |
#3
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Excel cell-formatting lost when mail-merging under Office 2007
Sorry - this does not work in Word 2007 (SP1). It says
!Syntax Error, { where the formatted number should be. RW "Graham Mayor" wrote: You need a calculated field to divide by 1000000 i.e. {={Mergefield Fieldname} / 1000000 \# "$,0.000m"} -- Graham Mayor - Word MVP My web site www.gmayor.com Word MVP web site http://word.mvps.org Ron West wrote: Hi, I've been given the task of fixing a Mail Merge that worked fine in Office 2000 but loses the Excel cell-formatting when run under Office 2007. I understand this is due to the new Office using some form of ADO instead of DDE to transfer the data. So, I've been looking at Word formatting pages such as http://www.gmayor.com/formatting_word_fields.htm but I'm still having a lot of trouble getting anything remotely complex to work in Word 2007. My need is to take a number such as 12345678000 from an Excel cell and format it in Word as £12,345.678m (ie, shown to 3 decimal places of millions). The original formatting string in Excel was £#,##0.000,,"m" (which seems to auto-divide by 1 million for display purposes as well as format the number) and I have tried to base my Word MERGEFIELD format string on this but with no success. I have so far only succeeded in getting the merged field to be formatted as £12,345,678,000 using \# "£#,##0" I have tried all sorts of formats and intermediate-value-formulae including "SET ..." and "/ 1000000" but nothing works. Can anyone help, please? |
#4
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Excel cell-formatting lost when mail-merging under Office 2007
You must enter the brackets with CTRL+F9. You cannot simply type them. It
works in 2007 just fine. -- Graham Mayor - Word MVP My web site www.gmayor.com Word MVP web site http://word.mvps.org Ron West wrote: Sorry - this does not work in Word 2007 (SP1). It says !Syntax Error, { where the formatted number should be. RW "Graham Mayor" wrote: You need a calculated field to divide by 1000000 i.e. {={Mergefield Fieldname} / 1000000 \# "$,0.000m"} -- Graham Mayor - Word MVP My web site www.gmayor.com Word MVP web site http://word.mvps.org Ron West wrote: Hi, I've been given the task of fixing a Mail Merge that worked fine in Office 2000 but loses the Excel cell-formatting when run under Office 2007. I understand this is due to the new Office using some form of ADO instead of DDE to transfer the data. So, I've been looking at Word formatting pages such as http://www.gmayor.com/formatting_word_fields.htm but I'm still having a lot of trouble getting anything remotely complex to work in Word 2007. My need is to take a number such as 12345678000 from an Excel cell and format it in Word as 12,345.678m (ie, shown to 3 decimal places of millions). The original formatting string in Excel was #,##0.000,,"m" (which seems to auto-divide by 1 million for display purposes as well as format the number) and I have tried to base my Word MERGEFIELD format string on this but with no success. I have so far only succeeded in getting the merged field to be formatted as 12,345,678,000 using \# "#,##0" I have tried all sorts of formats and intermediate-value-formulae including "SET ..." and "/ 1000000" but nothing works. Can anyone help, please? |
Reply |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
My zip code format is lost when merging from Excel to Word... | Microsoft Word Help | |||
My zip code format is lost when merging from Excel to Word... | Microsoft Word Help | |||
My zip code format is lost when merging from Excel to Word... | Microsoft Word Help | |||
Why is formating (zip & phone) lost when merging from Excel to Wo. | Mailmerge | |||
Carrying Excel cell properties into word when mail merging | Mailmerge |