Link cells in word doc (table) to update from 2 separate docs

larrens · #1 09-15-2014, 10:00 PM

I would like to have a Word “summary” document that can be set up to link or feed off table cells in other word documents.
I don’t mind if the update is automatic or needs a manual refresh of links.

I would like to do this to avoid double-keying lots of data & formats.

By way of example, I have included 3 attachments in a simplified format to illustrate what I am after.

“Dept A – detail” – contains a number of cells, but I want the 2 cells (text and format) under “Controlled Risk” to feed the Summary document.
“Dept B – detail” – contains a number of cells, but I want the 2 cells (text and format) under “Controlled Risk” to feed the Summary document.
“Summary” – I want the specific data I noted above (text and format under “Controlled Risk” from the 2 different documents (Dept A & Dept B detail) to feed the respective cells here.

My example is simple, but this will have a lot more data for what I need.

Also, happy to have the Word “detail” documents feed an Excel based summary if that is easier.
It’s just preferable to have the detail template in Word as there will be some freeform text which is best suited to a well formatted table.

Hope someone can help.

I use Word 2010.

gmayor · #2 09-15-2014, 10:27 PM

I think I would probably be inclined to add a macro to the summary that would grab the data and formatting from the source documents and optionally run it when the document is opened. Add the following code and save the document as a macro enabled document. The macro assumes all three documents will be in the same folder.
http://www.gmayor.com/installing_macro.htm

Code:

Sub UpdateSummary()
Dim oSourceA As Document
Dim oSourceB As Document
Dim oTarget As Document
Dim oTable As Table
    Set oTarget = ThisDocument
    Set oTable = oTarget.Tables(1)
    Set oSourceA = Documents.Open(FileName:=oTarget.Path & "\" & "Dept A - detail.docx", AddToRecentFiles:=False, Visible:=False)
    Set oSourceB = Documents.Open(FileName:=oTarget.Path & "\" & "Dept B - detail.docx", AddToRecentFiles:=False, Visible:=False)
    oTable.Cell(2, 3).Range.Text = oSourceA.Tables(1).Cell(2, 6).Range.Text
    oTable.Cell(2, 3).Shading.BackgroundPatternColor = oSourceA.Tables(1).Cell(2, 6).Shading.BackgroundPatternColor
    oTable.Cell(3, 3).Range.Text = oSourceA.Tables(1).Cell(3, 6).Range.Text
    oTable.Cell(3, 3).Shading.BackgroundPatternColor = oSourceA.Tables(1).Cell(3, 6).Shading.BackgroundPatternColor
    oSourceA.Close 0
    oTable.Cell(2, 4).Range.Text = oSourceB.Tables(1).Cell(2, 6).Range.Text
    oTable.Cell(2, 4).Shading.BackgroundPatternColor = oSourceB.Tables(1).Cell(2, 6).Shading.BackgroundPatternColor
    oTable.Cell(3, 4).Range.Text = oSourceB.Tables(1).Cell(3, 6).Range.Text
    oTable.Cell(3, 4).Shading.BackgroundPatternColor = oSourceB.Tables(1).Cell(3, 6).Shading.BackgroundPatternColor
    oSourceB.Close 0
    Set oSourceA = Nothing
    Set oSourceB = Nothing
    Set oTarget = Nothing
    Set oTable = Nothing
End Sub

Sub AutoOpen()
    UpdateSummary
End Sub

larrens · #3 09-15-2014, 10:40 PM

hi Gmayor

Thanks for the prompt reply
2 questions:
1) do I put the code in all 3 docs or only the "summary" doc?
2) Also, if I extend the number of rows to say 20 from the 2 in my example, is there any cchange to the code?

I will ensure all files are in the same folder.

gmayor · #4 09-15-2014, 11:20 PM

The code only goes in the summary.
The code assumes that the document names you provided are correct.

The names are used at

Set oSourceA = Documents.Open(FileName:=oTarget.Path & "\" & "Dept A - detail.docx", AddToRecentFiles:=False, Visible:=False)
Set oSourceB = Documents.Open(FileName:=oTarget.Path & "\" & "Dept B - detail.docx", AddToRecentFiles:=False, Visible:=False)

If you add more rows then you will need to process those rows also. The code for a single row from one of the sources is

Code:

oTable.Cell(2, 3).Range.Text = oSourceA.Tables(1).Cell(2, 6).Range.Text     
oTable.Cell(2, 3).Shading.BackgroundPatternColor = oSourceA.Tables(1).Cell(2, 6).Shading.BackgroundPatternColor

The syntax is .Cell(Row, Column)

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Is it possible to copy non-contiguous rows of a Table and paste them as a separate Table in Word?	Joey Cheung	Word Tables	1	08-12-2014 05:15 PM
Update Link very slow -PowerPoint Charts & object, cells etc Linked to Excel	johnseito	PowerPoint	0	01-28-2014 06:24 PM
Why does Word separate table and reference endnotes by allowing text to go in between	newby2013	Word Tables	2	12-31-2012 03:45 PM
How to link an object to a URL link to update automatically	expert4knowledge	Excel	1	06-11-2012 09:30 AM
Adding identical footers to 187 separate docs	sqzdog	Word	11	10-31-2011 01:46 AM