Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Keep excel format in mail merge 2007
I need to keep the formatting from excel where each number may have a
different number of decimal points and or currency symbol, so field switches are not helpful, at least not the ones I've come across. I have tried using the confirm file format conversion on open, as suggested by microsoft. This does not work if you have multiple worksheets, it only picks the first one. Any ideas? |
#2
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Keep excel format in mail merge 2007
Yes, the "format it in Word strategy" unravels when you have data like that.
There's not a lot you can do about the numbers unless you can use DDE (which would require you to move the sheet to the beginning of the workbook, or copy it to a new workbook), or unless you can get ODBC/OLE DB to see the numbers as text. You /may/ be able to do that using the following technique (from http://tips.pjmsn.me.uk/t0003.htm )... 1. Format a column as text via Data|Text to Columns... If you have a mixture of numbers and texts in a column but numbers in the first 8 rows, you may need to get the OLE DB provider to "see" the column as a text column. However, selecting the column and using Format|Cells to set the format as Text does not appear to be enough. One way that does appear to work is - select the column - select the Data|Text to Columns... menu option - click Next through the wizard until you reach Step 3 of 3, then select Text as the Column Data Format. If you're setting this stuff up for someone else to use, I don't think there's anything particularly simple you can do except try to ensure that you have the text columns you need. If you're doing it yourself, it might be easier to copy/paste the worksheet into a blank new Word document and use that as the data source (not sure if that will do everything you need, though). Another approach might be to maintain enough information in the Excel sheet to reconstitute the numbers/amounts correctly in Word. For example, if you save the currency symbol in a separate column (perhaps using an Excel worksheet function to take the text before the number and trim it) then you can insert it as a separate field. Working with different numbers of decimal places might well be trickier as it might be hard to create a worksheet function in Excel to work out reliably how many are intended to be in any given cell (I am pretty sure this would be harder if not imposible in Word), but suppose you have a separate column that records how many decimal places are intended - then you could use a nested field in the format switch in Word, e.g. { MERGEFIELD mynumber \#"0{ IF { MERGEFIELD mydp } = 1 ".0" "{ IF { MERGEFIELD mydp } = 2 ".00" "etc." }" }" } or perhaps simpler, something like: { SET mydp1 ".0" }{ SET mydp2 ".00" }{ SET mydp3 ".000" } etc. at the beginning of the document, then { MERGEFIELD mynumber \#"0{ REF "mydp{ MERGEFIELD mydp }" }" } (but I haven't checked that last one). -- Peter Jamieson http://tips.pjmsn.me.uk "tennisnut" wrote in message ... I need to keep the formatting from excel where each number may have a different number of decimal points and or currency symbol, so field switches are not helpful, at least not the ones I've come across. I have tried using the confirm file format conversion on open, as suggested by microsoft. This does not work if you have multiple worksheets, it only picks the first one. Any ideas? |
Reply |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
format field percentage merge Word 2007 from excel 2007 | Mailmerge | |||
Problem Maintaing Excel Format after Mail Merge | Mailmerge | |||
Preserve format of data in Excel when doing a mail merge | Mailmerge | |||
Format from Excel not being shown in mail merge | Mailmerge | |||
mail merge from excel that changes my format from UK to US format | Mailmerge |