Merging values into Word documents

[CottonRLS]

I used to do this in Office 2003 for years, but for the first time this year,
I have to create a merge document in Word to pull Currency values from an
Excel 2007 worksheet range. No matter what I do, Word imports the currency
numbers to 4 decimal places. No change in Sheet versus named range, field
name changes, formatting Excel data differently (0 versus 2 decimal places),
or any other change I can think of gives me properly formatted currency
numbers.

I know there are merge codes to deal with this, but why should I have to
know all of these arcane formatting codes when it should be able to b e
handled automatically?

Thanks in advance.

CottonRLS

 

[macropod]

Hi CottonRLS,

If Word's mergefields are showing the values to 4 decimal places, that's because the values are *stored* that way in Excel. Had you
rounded/trimmed the values to the appropriate number of decimal places in Excel, you wouldn't be having this problem.

Whether the formatting differences between Word and Excel are a 'problem' depends on your perspective. A distinct advantage that
flows from Word working with the unformatted values is that you are then free to format them there as you like, without being
constrained to whatever number formats appear in the data source.

 

[CottonRLS]

I've been aware that Excel stores full values, not displayed values since the
beginning. My only problem with it is if Word imports a cell's information
that is formatted for Currency, why is there no simple radio button to click
or box to check to preserve the formatting from source to destination?

For example, I had to get the formatting codes (\# "$ #,##0.00;($
#,##0.00)") from another user from a forum such as this since the only ones
listed in Word's dialog box to edit field codes were the following:

\b
\m
\f
\v

This seems to be a very incomplete list and I see no mention of the "\#"
switch anywhere in Help. Even a search of Help for "\#" yields no results.
Merging a letter with currency amount fields must happen very frequently.
Surely there could be a simple switch added to the list to deal with this one
thing...or a checkbox...or something similar.

Sure, I could edit every cell formula to use the RND function, but doesn't
that make using the spreadsheet more complex than it needs to be? Why even
bother to develop a cell format capability to display as Currency if any
other use of the information can't take advantage of it?

It has been at least a year since I last did this with Office 2003, but it
seems 2003 used a DDE connection to the *.xls file that preserved the
displayed format where Office 2007, by default, uses a "OLE DB Database file"
connection. Perhaps that's why I've never experienced this problem until
Office 2007.

Cotton

Hi CottonRLS,

If Word's mergefields are showing the values to 4 decimal places, that's because the values are *stored* that way in Excel. Had you
rounded/trimmed the values to the appropriate number of decimal places in Excel, you wouldn't be having this problem.

Whether the formatting differences between Word and Excel are a 'problem' depends on your perspective. A distinct advantage that
flows from Word working with the unformatted values is that you are then free to format them there as you like, without being
constrained to whatever number formats appear in the data source.

--
Cheers
macropod
[MVP - Microsoft Word]

I used to do this in Office 2003 for years, but for the first time this year,
I have to create a merge document in Word to pull Currency values from an
Excel 2007 worksheet range. No matter what I do, Word imports the currency
numbers to 4 decimal places. No change in Sheet versus named range, field
name changes, formatting Excel data differently (0 versus 2 decimal places),
or any other change I can think of gives me properly formatted currency
numbers.

I know there are merge codes to deal with this, but why should I have to
know all of these arcane formatting codes when it should be able to b e
handled automatically?

Thanks in advance.

CottonRLS

 

[macropod]

Hi CottonRLS,

Since you're trying toformat field results, perhaps you should have checked 'field format' in Word's Help file. There you'll find a
topic 'Apply formatting to information inserted by fields'.

FWIW, You'd probably find that the formatting switch would work better as \# "$ ,0.00;($,0.00)"

And yes, it would have been helpful had MS made mailmerge field insertion a bit smarter by offering the available formatting options
as part of that process.

--
Cheers
macropod
[MVP - Microsoft Word]

I've been aware that Excel stores full values, not displayed values since the
beginning. My only problem with it is if Word imports a cell's information
that is formatted for Currency, why is there no simple radio button to click
or box to check to preserve the formatting from source to destination?

For example, I had to get the formatting codes (\# "$ #,##0.00;($
#,##0.00)") from another user from a forum such as this since the only ones
listed in Word's dialog box to edit field codes were the following:

\b
\m
\f
\v

This seems to be a very incomplete list and I see no mention of the "\#"
switch anywhere in Help. Even a search of Help for "\#" yields no results.
Merging a letter with currency amount fields must happen very frequently.
Surely there could be a simple switch added to the list to deal with this one
thing...or a checkbox...or something similar.

Sure, I could edit every cell formula to use the RND function, but doesn't
that make using the spreadsheet more complex than it needs to be? Why even
bother to develop a cell format capability to display as Currency if any
other use of the information can't take advantage of it?

It has been at least a year since I last did this with Office 2003, but it
seems 2003 used a DDE connection to the *.xls file that preserved the
displayed format where Office 2007, by default, uses a "OLE DB Database file"
connection. Perhaps that's why I've never experienced this problem until
Office 2007.

Cotton

Hi CottonRLS,

If Word's mergefields are showing the values to 4 decimal places, that's because the values are *stored* that way in Excel. Had
you
rounded/trimmed the values to the appropriate number of decimal places in Excel, you wouldn't be having this problem.

Whether the formatting differences between Word and Excel are a 'problem' depends on your perspective. A distinct advantage that
flows from Word working with the unformatted values is that you are then free to format them there as you like, without being
constrained to whatever number formats appear in the data source.

--
Cheers
macropod
[MVP - Microsoft Word]

I used to do this in Office 2003 for years, but for the first time this year,
I have to create a merge document in Word to pull Currency values from an
Excel 2007 worksheet range. No matter what I do, Word imports the currency
numbers to 4 decimal places. No change in Sheet versus named range, field
name changes, formatting Excel data differently (0 versus 2 decimal places),
or any other change I can think of gives me properly formatted currency
numbers.

I know there are merge codes to deal with this, but why should I have to
know all of these arcane formatting codes when it should be able to b e
handled automatically?

Thanks in advance.

CottonRLS

 

[Graham Mayor]

The full range of switches is documented at
http://www.gmayor.com/formatting_word_fields.htm

--
<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
Graham Mayor - Word MVP


<>>< ><<> ><<> <>>< ><<> <>>< <>><<>

I've been aware that Excel stores full values, not displayed values
since the beginning. My only problem with it is if Word imports a
cell's information that is formatted for Currency, why is there no
simple radio button to click or box to check to preserve the
formatting from source to destination?

For example, I had to get the formatting codes (\# "$ #,##0.00;($
#,##0.00)") from another user from a forum such as this since the
only ones listed in Word's dialog box to edit field codes were the
following:

\b
\m
\f
\v

This seems to be a very incomplete list and I see no mention of the
"\#" switch anywhere in Help. Even a search of Help for "\#" yields
no results. Merging a letter with currency amount fields must happen
very frequently. Surely there could be a simple switch added to the
list to deal with this one thing...or a checkbox...or something
similar.

Sure, I could edit every cell formula to use the RND function, but
doesn't that make using the spreadsheet more complex than it needs to
be? Why even bother to develop a cell format capability to display as
Currency if any other use of the information can't take advantage of
it?

It has been at least a year since I last did this with Office 2003,
but it seems 2003 used a DDE connection to the *.xls file that
preserved the displayed format where Office 2007, by default, uses a
"OLE DB Database file" connection. Perhaps that's why I've never
experienced this problem until Office 2007.

Cotton

Hi CottonRLS,

If Word's mergefields are showing the values to 4 decimal places,
that's because the values are *stored* that way in Excel. Had you
rounded/trimmed the values to the appropriate number of decimal
places in Excel, you wouldn't be having this problem.

Whether the formatting differences between Word and Excel are a
'problem' depends on your perspective. A distinct advantage that
flows from Word working with the unformatted values is that you are
then free to format them there as you like, without being
constrained to whatever number formats appear in the data source.

--
Cheers
macropod
[MVP - Microsoft Word]

I used to do this in Office 2003 for years, but for the first time
this year, I have to create a merge document in Word to pull
Currency values from an Excel 2007 worksheet range. No matter what
I do, Word imports the currency numbers to 4 decimal places. No
change in Sheet versus named range, field name changes, formatting
Excel data differently (0 versus 2 decimal places), or any other
change I can think of gives me properly formatted currency numbers.

I know there are merge codes to deal with this, but why should I
have to know all of these arcane formatting codes when it should be
able to b e handled automatically?

Thanks in advance.

CottonRLS

 

[CottonRLS]

Thanks for approaching this topic. I took your advice. I searched for "field
format" in Word's help file. No topic titled "Apply formatting to information
inserted by fields" appeared. Not in the top 100 listings of search results.
Nothing with that title appeared even when I searched for "Apply formatiing
to information inserted by fields".

At this time, I've having a hard time understanding how we can be talking
about the same program, since you evidently have access to more help from the
program than I do.

Do you have some sort of whiz-bang "Developer" edition and have I been stuck
with the "you're on your own, Bozo!" edition? Just my luck!

Cotton

 

[CottonRLS]

I appreciate you passing me the link to the site that explains these fields.
It's a great resource, the kind we used to get in something they called "the
manual."

Just so you know, I only chose a rating of No, this did not answer my
question, because the thrust of my question and source of frustration was why
can I not find, within the program, a way to make this work?

Having to rely on a forum like this to do something so (in my mind, anyway)
straightforward shames the designer of the Help system. How would anyone know
your web-site with the requisite information was even there if they didn't
come to this forum first?

Nothing would ever get done properly without you guys.

Cotton

The full range of switches is documented at
http://www.gmayor.com/formatting_word_fields.htm

--
<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
Graham Mayor - Word MVP


<>>< ><<> ><<> <>>< ><<> <>>< <>><<>

I've been aware that Excel stores full values, not displayed values
since the beginning. My only problem with it is if Word imports a
cell's information that is formatted for Currency, why is there no
simple radio button to click or box to check to preserve the
formatting from source to destination?

For example, I had to get the formatting codes (\# "$ #,##0.00;($
#,##0.00)") from another user from a forum such as this since the
only ones listed in Word's dialog box to edit field codes were the
following:

\b
\m
\f
\v

This seems to be a very incomplete list and I see no mention of the
"\#" switch anywhere in Help. Even a search of Help for "\#" yields
no results. Merging a letter with currency amount fields must happen
very frequently. Surely there could be a simple switch added to the
list to deal with this one thing...or a checkbox...or something
similar.

Sure, I could edit every cell formula to use the RND function, but
doesn't that make using the spreadsheet more complex than it needs to
be? Why even bother to develop a cell format capability to display as
Currency if any other use of the information can't take advantage of
it?

It has been at least a year since I last did this with Office 2003,
but it seems 2003 used a DDE connection to the *.xls file that
preserved the displayed format where Office 2007, by default, uses a
"OLE DB Database file" connection. Perhaps that's why I've never
experienced this problem until Office 2007.

Cotton

Hi CottonRLS,

If Word's mergefields are showing the values to 4 decimal places,
that's because the values are *stored* that way in Excel. Had you
rounded/trimmed the values to the appropriate number of decimal
places in Excel, you wouldn't be having this problem.

Whether the formatting differences between Word and Excel are a
'problem' depends on your perspective. A distinct advantage that
flows from Word working with the unformatted values is that you are
then free to format them there as you like, without being
constrained to whatever number formats appear in the data source.

--
Cheers
macropod
[MVP - Microsoft Word]


I used to do this in Office 2003 for years, but for the first time
this year, I have to create a merge document in Word to pull
Currency values from an Excel 2007 worksheet range. No matter what
I do, Word imports the currency numbers to 4 decimal places. No
change in Sheet versus named range, field name changes, formatting
Excel data differently (0 versus 2 decimal places), or any other
change I can think of gives me properly formatted currency numbers.

I know there are merge codes to deal with this, but why should I
have to know all of these arcane formatting codes when it should be
able to b e handled automatically?

Thanks in advance.

CottonRLS

 

[macropod]

Hi CottonRLS,

That reference I gave was for a very ordinary installation of Word 2000. I believe the same entry appears in Word 2003. Even if it
doesn't, I'm sure something similar would be returned via the 'field format' query, or even just a 'field' query.