Re: Linking Specific Cells to a Word Document for a Report Template

john9088 Insert a { DOCVARIABLE A45 } in the location(s) in the template where you want the data to appear and insert the following code into an AutoNew macro

 

Sub AutoNew()
Dim FD As FileDialog
Dim strSource As String
Dim bStartApp As Boolean
Set FD = Application.FileDialog(msoFileDialogFilePicker)
With FD
    .Title = "Select the workbook that contains the data"
    .Filters.Clear
    .Filters.Add "Excel Workbooks", "*.xlsx"
    .AllowMultiSelect = False
    If .Show = -1 Then
        strSource = .SelectedItems(1)
    Else
        MsgBox "You did not select the workbook that contains the data"
        Exit Sub
    End If
End With
On Error Resume Next
Set xlapp = GetObject(, "Excel.Application")
If Err Then
    bStartApp = True
    Set xlapp = CreateObject("Excel.Application")
End If
On Error GoTo 0
Set xlbook = xlapp.Workbooks.Open(strSource)
Set xlsheet = xlbook.Worksheets(1)
With ActiveDocument
    .Variables("A45").Value = xlsheet.Range("A45").Value
    .Range.Fields.Update
End With
xlbook.Close
If bStartApp = True Then
    xlapp.Quit
End If
Set xlapp = Nothing
Set xlbook = Nothing
Set xlsheet = Nothing
End Sub

When you create a new document from that template, a File Open dialog will appear by means of which you need to select the Workbook that contains the data after you have done that and click on Open in that dialog, the data from the workbook will appear in the document.