How do I create an automatic currency update field in Word

[Middopman]

I produce property specifications for european properties using Word. I want
to be able to enter a value in €'s that is then automatically converted to
£'s based upon an exchange rate contained in a field in an external Excel
file. Both values need to be displayed in the word document. What I dont
know is what format the €'s value needs to be entered in and then how to set
up the equation/formula. I would like the final layout to look like this...
....€300,000 / £200,000 , if possible.
If this isn't possible any other suggestoins about how to achieve the same
result would also be welcome.

 

[Doug Robbins]

For information on how to get the exchange rate, see the article "Control
Excel from Word" at:
http://word.mvps.org/FAQs/InterDev/ControlXLFromWord.htm

Assuming that you are using formfields in a protected document, to get it
all to work, you would run a macro on exit from the Euro formfield that got
the amount entered into that field by use of the .Result property of the
formfield, then gets the exchange rate from Excel and then puts the product
of the two into the Pound formfield, once again using the .Result property.


--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

 

[Chuck]

I’m sure there’s a simpler/more elegant way of doing this, but here’s one
thought. This doesn’t require form fields (or protection).

Create a macrobutton field that references the code below (edit the code to
reference the cell that contains the exchange rate). For instance your macro
button code could read:

{ MACROBUTTON GetExchangeRate “Euro/Sterling exchange rate: “ }

and would display in the document as “Euro/Sterling exchange rate: “.

When you double click the macro button it will get the exchange rate from
the spreadsheet, store it in a form field and then update all the fields in
your document. There are of course other ways to run the macro, that’s up to
you.

Create a form field name it “EuroStgRate†– this is where the result
returned by the macro is stored.

Create an Ask field that asks for the price and stores it at bookmark “Priceâ€.

Create a Quote field that contains two fields:
1. a Ref field that refers to bookmark Price
2. followed by a slash (/)
3. a formula field with the formula: = Price * EuroStgRate \# "€#,##0.00"

so you end up with

{ ASK Price "What is the price?" \* MERGEFORMAT }{QUOTE {{ REF Price }/{ =
Price * EuroStgRate \# "€#,##0.00" }}

You can copy the Quote field to wherever in the document you need to show
the price in Stg and Euro.

HTH


Sub GetEuroStgRate()

On Error GoTo errorhandler

Dim objExcel As Object
Dim i As Long
Dim x As Long
Dim rngRange As Range
Dim curEuroStgRate As Currency

Set objExcel = CreateObject("Excel.Application")
objExcel.Workbooks.Open ("c:\exchangerates.xls")

Set myrange = objExcel.Worksheets("Sheet1").Range("B1:B1")

curEuroStgRate = myrange.Value

Set rngRange = ActiveDocument.Bookmarks("EuroStgRate").Range

rngRange.Text = curEuroStgRate

ActiveDocument.Bookmarks.Add "EuroStgRate", rngRange

With objExcel
.DisplayAlerts = False
.ActiveWorkbook.Close SaveChanges:=False
.DisplayAlerts = True
End With

objExcel.Application.Quit
Set objExcel = Nothing

ActiveDocument.Fields.Update

Exit Sub

errorhandler:

If objExcel Is Nothing Then
'do nothing
Else
objExcel.ActiveWorkbook.Close False
objExcel.Application.Quit
Set objExcel = Nothing
End If

MsgBox Err.Number & " " & Err.Description

Exit Sub

End Sub

 

[Chuck]

Oops.

When I said

Create a form field name it “EuroStgRate†– this is where the result
returned by the macro is stored.

what I *meant* to say was "create a BOOKMARK named "EuroStgRate"

Sorry for the mistake.