embedding an excel file into word and intelligently linking it the template

[ob1b]

Hi All,

Please bear with me... its my first post and my technical knowledge is... lacking somewhat!

I currently have calculations in excel and a word template which I have linked by adding comments to each cell in excel and then in word setting up the doc properties and inserting fields so when I run the code below from excel I can generate different calc docs in word for different variables.... Thats all hunky dory but it means for each document I create I have to the manage to documents - the exel file and the word doc.

What I really want to do is end up with a single file in which I can enter the variables that will run through the calcs ive set up in excel and then link the results into the template.

Ive started by embedding the excel file within word which is fine... but then how can a modify this code so I can change the variables in the embedded excel table to link it to the correct places in the word doc?

Sub WriteVariables()

Dim commrange As Range
Dim mycell As Range
Dim i As Integer
Dim objword
Dim output(1, 200) As String
Dim filepath As String
filepath = "D:\Data\Documents\My Documents\*INSERT FILENAME HERE*.doc"

Set commrange = Sheets("Sheet1").Cells.SpecialCells(xlCellTypeComments)
i = 0
    For Each mycell In commrange
         On Error Resume Next
            
            If InStr(1, mycell.Comment.Text) <> "" Then
                
                output(0, i) = mycell.Comment.Text
                output(1, i) = mycell.Text
            i = i + 1
            Else
            End If
    Next mycell
                
Set objword = CreateObject("Word.Application")
    objword.Visible = True
    
    Set objDoc = objword.Documents.Open(filepath)
    
For i = 0 To 200

objword.ActiveDocument.CustomDocumentProperties(output(0, i)).Value = output(1, i)

Next i

objword.ActiveDocument.Fields.Update

End Sub