Jul 02 2023 10:00 PM
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.
Jul 03 2023 12:33 AM
@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.