Reply
 
Thread Tools Display Modes
  #1   Report Post  
Posted to microsoft.public.word.mailmerge.fields
tennisnut tennisnut is offline
external usenet poster
 
Posts: 1
Default 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   Report Post  
Posted to microsoft.public.word.mailmerge.fields
Peter Jamieson Peter Jamieson is offline
external usenet poster
 
Posts: 4,582
Default 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

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
format field percentage merge Word 2007 from excel 2007 LoisInAlaska Mailmerge 3 September 15th 08 06:47 PM
Problem Maintaing Excel Format after Mail Merge Mike Mailmerge 16 August 22nd 08 10:31 AM
Preserve format of data in Excel when doing a mail merge Louise Mailmerge 1 January 13th 08 10:37 AM
Format from Excel not being shown in mail merge Helen Mailmerge 2 November 9th 05 08:14 PM
mail merge from excel that changes my format from UK to US format mark Mailmerge 0 January 21st 05 04:01 PM


All times are GMT +1. The time now is 09:16 AM.

Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 Microsoft Office Word Forum - WordBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Word"