Forum Discussion

john9088's avatar
john9088
Copper Contributor
Jul 03, 2023

Linking Specific Cells to a Word Document for a Report Template

Hi! I make several reports a month require a lot of calculations and formulas from Excel, and then inputting that data into a report form (100+ pages). This data always comes from the same cells in the Spreadsheet, so I'd like to create a template that can save me the trouble of manually entering this. 

 

I know about pasting as a link to the spreadsheet, but given that I am trying to create a template that can be duplicated and renamed to the relevant month, the linking has broken several times as a result of moving, renaming, and duplicating files. I've also tried the mail merge feature but hasn't produced the desired result. 

 

In a nutshell, I want the word document to insert the exact cell (ie. Worksheet 1 Cell A45) from a given spreadsheet into a location on the Word file, preferably formatted in the correct font (Arial font size 12), and have this linked template be reusable report after report. 

 

Thank you for your time and I look forward to your responses.

1 Reply

  • Doug_Robbins_Word_MVP's avatar
    Doug_Robbins_Word_MVP
    MVP
    Jul 03, 2023

    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.  

Resources