Linking excel tables in Word 2007

[Mac NorLog]

Can someone help me with what I think should be an easy task but is proving
anything in Word 2007. I use documents in word that contain excel tables
to hold data that require formula. The results in the first table affect the
data presented in the second table & so on. I assumed that a simple "Paste
Link" would do the trick but although I can do the link when changing the
data in the first table, the data in the second changes the first time but
any subsequent changes are not registered & the second table remains
unchanged. When I then go into the table to view the cell formula there is a
reference to the
first table but the cell address appears as RxCx & then displays an error
##REF in the cell. Having followed some of the discussions I have upgraded
with all the current upgrades for Office, however, this only had the effect
of causing my original file to become corrupt & cannot now be open.
It appears that once the file is saved that the link becomes static &
therefore doesn't change with the source data. It has been suggested that
this can only be solved by use of macro's, an area I am not familiar with.
Can anyone help?

 

[Graham Mayor]

It is impossible to recreate your conditions without more information. You
have pasted a link to an Excel table - OK, but then what are you doing with
it? Can you be a bit more specific about the nature of your subsequent
table(s) and the formulae (in Word?).

--
<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
Graham Mayor - Word MVP


<>>< ><<> ><<> <>>< ><<> <>>< <>><<>

 

[Mac NorLog]

Hi Graham I will try.
I have a Word 2007 Document with two embedded excel tables. In the first is
a breakdown of costs for a quote, listing such items as walls, floor, doors &
windows with separate prices for each. In the second table I have a list of
components which will be supplied for each of the sections in table 1. In the
cell adjacent to the list in table 2 I want a tick or a cross to appear to
denote whether or not the component is included, this is dependent upon the
corresponding section in table 1 having a figure greater than 0. I have
pasted a link from the appropriate cell in table 1 into table 2 & when first
doing this the tick or cross appears as required. However if I change the
cell in table 1 the correspond cell in table 2 rmains unchanged despite the
paste link suggesting that it should change.
If this doesn't make sense can I email you the file?
Thanks Mark

 

[Graham Mayor]

From your description I suspect one of two things appears to be happening

1. The link is broken to the first table when you update the link, the
correction for that would be to link the second table back to the original
Excel table and not to the first table.
2. The second link is not being updated when you change the first table
content. What happens if you use CTRLA then F9 to force an update?

It would probably be better to do all the table work in Excel then link the
resulting table to Word

--
<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
Graham Mayor - Word MVP


<>>< ><<> ><<> <>>< ><<> <>>< <>><<>

 

[macropod]

Hi Mac,

I've been doing some more work on this. Here's a vba solution. It's a bit clunky but it seems to work OK:
Sub OLEUpdate()
Dim objOLE As Word.OLEFormat
Dim xlVal
Application.ScreenUpdating = False
With ActiveDocument
Set objOLE = .Shapes("Object 1").OLEFormat
objOLE.DoVerb (wdOLEVerbInPlaceActivate)
xlVal = objOLE.Object.ActiveSheet.Range("A1").Value
Set objOLE = .Shapes("Object 2").OLEFormat
objOLE.DoVerb (wdOLEVerbInPlaceActivate)
objOLE.Object.ActiveSheet.Range("B2").Value = xlVal
Set objOLE = .Shapes("Object 1").OLEFormat
objOLE.DoVerb (wdOLEVerbInPlaceActivate)
End With
Application.ScreenUpdating = True
End Sub
The above code takes a value from A1 in 'Object 1' and copies it to 'B2' in 'Object 2'. I've used Shape objects, rather than in-line
shape objects, as that's how I anticipate your Excel worksheets will be. you'll need to change the object names in the code to match
whatever your's are called.

It used to be that you could use 'SendKeys "{ESC}"' to de-activate an embedded object - MS has never provided an intelligent way of
doing this. And with Windows Vista, SendKeys no longer works. The work-around I've found is to re-activate the 1st object.

You could probably automate the process a bit further by using an InputBox, for example, to solicit whatever value you need to
update the 1st object, before reading the cell from which you need to update the 2nd object.

Hope this helps.