If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Merging values into Word documents
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 |
#2
|
|||
|
|||
Merging values into Word documents
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] "CottonRLS" wrote in message ... 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 |
#3
|
|||
|
|||
Merging values into Word documents
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 "macropod" wrote: 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] "CottonRLS" wrote in message ... 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 |
#4
|
|||
|
|||
Merging values into Word documents
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] "CottonRLS" wrote in message ... 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 "macropod" wrote: 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] "CottonRLS" wrote in message ... 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 |
#5
|
|||
|
|||
Merging values into Word documents
The full range of switches is documented at
http://www.gmayor.com/formatting_word_fields.htm -- Graham Mayor - Word MVP My web site www.gmayor.com Word MVP web site http://word.mvps.org CottonRLS wrote: 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 "macropod" wrote: 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] "CottonRLS" wrote in message ... 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 |
#6
|
|||
|
|||
Merging values into Word documents
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 "macropod" wrote: 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] |
#7
|
|||
|
|||
Merging values into Word documents
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 "Graham Mayor" wrote: The full range of switches is documented at http://www.gmayor.com/formatting_word_fields.htm -- Graham Mayor - Word MVP My web site www.gmayor.com Word MVP web site http://word.mvps.org CottonRLS wrote: 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 "macropod" wrote: 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] "CottonRLS" wrote in message ... 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 |
#8
|
|||
|
|||
Merging values into Word documents
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. -- Cheers macropod [MVP - Microsoft Word] "CottonRLS" wrote in message ... 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 "macropod" wrote: 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] |
Thread Tools | |
Display Modes | |
|
|