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" <tennisnut[ at ]discussions.microsoft.com> wrote in message
news:E09EA9A3-6244-419D-8621-8A22C17DA1DE[ at ]microsoft.com...
[Quoted Text] >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?
|