[Lotiana]

You are an angel, Graham, what I call a PROFESSIONAL!
As soon as I get over the trouble with installing VISTA SP1 (a whole day of
failures already and counting...blush...blush...blush...) I'll apply it;
you'll be the first to hear.
Thank you so much

"Graham Mayor" wrote:

OK, not quite a eureka moment, but the following should work:
It formats numbers in the format #.## or # in the third table column (set at
iCol = 3)
to use either GBP or EUR currency separators. Calculated fields are updated
and converted to text before formatting.

I have crossposted to the vba general forum in case someone there has a
simpler solution.


Sub FormatColumnAsCurrency()
Dim cTable As Table
Dim oRng As Range
Dim sNum, sCent, sEuro, sTEuro, sMEuro As String
Dim sSep, sDec, sCurr, sSign As String
Dim iCol As Integer
Dim i As Long
Dim count As Variant

'Set column number containing the amounts
iCol = 3

sCurr = InputBox("Enter Y to format table as Euro" & vbCr & _
"Enter anything else to format as Sterling", _
"Format currency", "Y")

Set cTable = ActiveDocument.Tables(1)
For i = 1 To cTable.Rows.count
Set oRng = cTable.Cell(i, iCol).Range
On Error Resume Next
With oRng.Fields
.Update
.Unlink
End With
Next i

For i = 1 To cTable.Rows.count
Set oRng = cTable.Cell(i, iCol).Range
sNum = Replace(oRng, Chr(13) & Chr(7), "")
sNum = Replace(sNum, ",", "")
If InStr(1, sNum, ".") = False Then
sNum = sNum & ".00"
End If

If UCase(sCurr) = "Y" Then
sSep = "."
sDec = ","
sSign = "EUR "
Else
sSep = ","
sDec = "."
sSign = "GBP "
End If

sCent = sDec & Right(sNum, 2)
sEuro = Right(Int(sNum), 3)
If sNum 999999 Then
sMEuro = Right(Int(sNum / 1000000), 3) & sSep
Else
sMEuro = ""
End If
If sNum 999 Then
sTEuro = Right(Int(sNum / 1000), 3) & sSep
Else
sTEuro = ""
End If

sResult = Replace(oRng, sNum, sSign & sMEuro & sTEuro & sEuro & sCent)
sResult = sSign & sMEuro & sTEuro & sEuro & sCent
oRng = Replace(sResult, Chr(13), "")
Next i
End Sub


--

Graham Mayor - Word MVP

My web site www.gmayor.com
Word MVP web site http://word.mvps.org



Graham Mayor wrote:
This has been bugging me The best I can come up with so far is:

{ Set A "{ =INT({ =SUM(ABOVE) })}" }{ Set B "{ ={ =SUM(ABOVE) } - {
=INT({ =SUM(ABOVE) }) } }" }{ IF{ A } 999999 "{ A \# "EUR
#'.'###'.'###" }" "{ IF{ A } 999 "{ A \# "EUR #'.'###" }" "{ A \#
"EUR #" }" }" }{ ={ B } * 100 \# "','00"}

which will format the total i.e. { =SUM(ABOVE) }
in Euro format on UK Regional windows.

It should also be possible to fix the fields in a table as text then
present the numbers in Euro format using vba. If I come up with a
solution to that I will post back.


Graham Mayor wrote:
Unfortunately that isn't possible with fields because the separators
are determined by Windows regional settings.


Lotiana wrote:
Hi again Graham,
What I'd like to do is about "static" representation/display rather
than dynamic/real-time computation with the instant exchange rate,
namely our here usual \# "EUR ,0.00" for some fields, and their \#
"EUR .0,00" (decimal point is comma) for others.

"Graham Mayor" wrote:

It's not a bother, but an intriguing challenge of the type that
some of us thrive on However, I am still not clear on whether
you want the table to make the conversion from Sterling to Euros,
and if you do what you are going to use as an exchange rate, which
as you know goes up and down like a bride's nightie.

If you are creating invoices for British and European clients, then
it shouldn't be too difficult to use to ensure that you have the
right prices on your documents. It's a little more difficult if you
want to only have the right currency and the correct value of that
currency shown, but it is not impossible.

If you use an ASK field for the exchange rate

eg { Ask Rate "Exchange Rate" \d "1.3056448789"}

then

{ ={ =SUM(LEFT) } * { Rate } \# "EUR ,0.00"}

in the cell to the right of the GBP total will give you the Euro
amount (assuming there are no other amounts on that row).

--

Graham Mayor - Word MVP

My web site www.gmayor.com
Word MVP web site http://word.mvps.org



Lotiana wrote:
Graham, it's about the very unsophisticated invoices I send from
the UK to our clients on the Continental Europe - a small table
I'd like to keep Word-only.

So far, I "compromised": one column displays GBP 1,000.00, the
corresponding one EUR 1,600.00 (with number format {=SUM(ABOVE) \#
"EUR #,##0.00"}).

With so many beautiful functions/features of Word, I would have
thought that the other format may co-exist too (like a column with
formulas with the other number format, displaying EUR 1.600,00
with, say, {=SUM(ABOVE) \# "EUR #.##0,00"}).

Sorry for bothering you, Graham, I don't want to waste your time;
not bad if not possible: clients got used to it anyway (otherwise
I'd had used Excel); I just wanted to imitate instantly the double
display in Amazon.co.uk's invoices, and to write to a supplier
from the Continent a letter with a calculation without changing
first Regional settings in Control Panel. But I'm only doing this
sort of work rarely, it's not among my main tasks.


"Graham Mayor" wrote:

How is Word to know whether the number is GBP or EUR?
Are the relative values of the currencies relevant?
We need more information about exactly what you are trying to
achieve.

--

Graham Mayor - Word MVP

My web site www.gmayor.com
Word MVP web site http://word.mvps.org



Lotiana wrote:
How can I get in the same table both GBP 9,999.99 and EUR
9.999,99 ? "Regional settings" either/or don't help!