Microsoft Office Forums - View Single Post - Link cells in word doc (table) to update from 2 separate docs

gmayor · 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