Making one change to change all pages in Word 2007

C

Cathy

I created a tax form for our City using Excel in the Word document. I need to
make the a rate change. Is there a way to make the rate change on the 1st
page and have it make the same change on all of the remaining pages without
changing each page?
 
J

Jean-Guy Marcil

Cathy said:
I created a tax form for our City using Excel in the Word document. I need to
make the a rate change. Is there a way to make the rate change on the 1st
page and have it make the same change on all of the remaining pages without
changing each page?
Click to expand...

More information will be needed.

Where is this rate? In the embeded Excel objects, in the Word document main
story text, in the header...???
How is this rate used? Is it part of formulas, but not directly seen in the
document itself...
Have you tried Find/Replace?
 
C

Cathy

The Tax Rate is embeded in the Excel object. It is part of the formula and
is not directly seen in the document itself. I have not tried Find/Replace
yet. Thanks for your help.
 
M

macropod

Hi Cathy,

If you open up the embedded Excel object and select the 'tax rate' cell, you can copy that, close the Excel object and paste the
cell contents as a link wherever you want in the document. You do this via Edit|Paste Special. When pasting, Word also gives you a
choice of paste formats - I'd suggest the formatted or unformatted text options, depending on your requirements. After pasting once,
you can simply make a copy of the pasted data then paste that wherever else you want in the document. Thereafter, updating the
values throughout your document will be a simple matter of updating the value in the Excel object, then selecting the whole document
(Ctrl-A) and pressing F9 (printing the document or doing a Print Preview should also work if you've got the 'update fields' option
checked unto Tools|Options|Print).

Cheers
 
J

Jean-Guy Marcil

Cathy said:
The Tax Rate is embeded in the Excel object. It is part of the formula and
is not directly seen in the document itself. I have not tried Find/Replace
yet. Thanks for your help.
Click to expand...

Do you mean that you have many embeded Excel objects, each using the same
Tax Rate, and that you need to change the tax rate in all those embeded
objects?
 
C

Cathy

Yes, each tax billl has an embeded Excel object (house valuation times the
current tax rate per $100). The only thing I have to change is the tax rate
on each bill. All bills are under one MS document, one tax bill for each
page. This way all I have to do each year is change the rate if it changes
or change the owner when a home sells. If anyone knows of an easier way to
achieve this, I am always up for an easier way. I just thought this would be
the easiest and keep each years taxes in one document.
 
J

Jean-Guy Marcil

Cathy said:
Yes, each tax billl has an embeded Excel object (house valuation times the
current tax rate per $100). The only thing I have to change is the tax rate
on each bill. All bills are under one MS document, one tax bill for each
page. This way all I have to do each year is change the rate if it changes
or change the owner when a home sells. If anyone knows of an easier way to
achieve this, I am always up for an easier way. I just thought this would be
the easiest and keep each years taxes in one document.
Click to expand...

You will need a macro to do this.

Sub UpdateTaxRate()

'Set a reference to Microsoft Excel "x.0" Object Library from _
Tools > References... in the VBA editor window

Dim ishpExcel As InlineShape
Dim oOle As Word.OLEFormat
Dim objExcel As Excel.Workbook
Dim rngCurrent As Range

Application.ScreenUpdating = False

Set rngCurrent = Selection.Range

For Each ishpExcel In ActiveDocument.InlineShapes
With ishpExcel
.OLEFormat.Activate
Set objExcel = .OLEFormat.Object
With objExcel.ActiveSheet
'Assuming tax rate is in cell located _
at third row and second column
.Cells(3, 2).Value = 11.5
End With
Set objExcel = Nothing
End With
Next

SendKeys "{ESC}"

rngCurrent.Select

Application.ScreenRefresh
Application.ScreenUpdating = False

End Sub


If you need help with using macros, see
http://word.mvps.org/faqs/macrosvba/CreateAMacro.htm
 
Top