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

gmayor · #1 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 · #2 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.

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