Linked Excel/Word Template?

[seanrm100_]

Hey there,

I was wondering what the best practice is for dynamically linking certain tables from an Excel book to a Word doc, then copying both the Excel book and Word doc to new files, while maintaining the links between the two. 

For example, I need to generate reports for technical studies. The reports are generally similar to one another, except for the data. I have Excel book templates to do the calculations, so I want to keep a report template Word doc, so when I start a new project, I can just copy over the Excel and Word docs to a new folder, rename at least the Word doc, update the inputs to the excel sheet and have it spit out the output tables in the report. I've been testing it out and it seems a bit messy. If I leave the Excel workbook name the same, I get caught in an infinite loop of "Excel can't have two workbooks with the same name open" even when the previous book is closed. If I rename it, then change all the link sources together, it updates the source book but forgets which tables were linked. 

Anyone have success with this? Thanks!

[#2]

Does anybody have experience with this?

[#3]

I suggest that you save both Excel and Word files in the same folder. Also, ensure that the paste link in the Word file is updated to reflect the new location of the Excel file.

[#4]

What if there are like 100 links? Using the links manager I can update the file one by one, but it seems buggy and some of them start pointing to the wrong table. Is there any way the links can just be a relative location instead of absolute? As in link to a file called "ExcelInput.xlsm" in the same folder as the word doc. Then when you copy both to a new folder and leave the excel name alone it would keep the links but look for the excel file in the same folder.

[#5]

@seanrm100_ Hey Sean, I'm having the exact same issue as you did. I'm trying to link my Word template to my excel file data but I have to update my connections for each new report, which eliminate the benefits of linking data in the first place. Have you found a solution?

Thanks,

Nicolas

[#6]

@seanrm100_ 

Create a cross-reference

A cross-reference allows you to link to other parts of the same document. For example, you might use a cross-reference to link to a chart or graphic that appears elsewhere in the document. The cross-reference appears as a link that takes the reader to the referenced item.

If you want to link to a separate document you can create a hyperlink.

Thank you for your understanding and patience

Nikolino

I know I don't know anything (Socrates)

[#7]

@NikolinoDE 

Another proposed solution with VBA.

Open a Word document using a command button, into which the data is inserted that can be seen on the worksheet on which the command button is located.

The corresponding file should be opened without any problems with the following code:

Private Sub CommandButton1_Click ()
Dim is As Object
     Dim abc As Integer
    
     Dim new Doc As Object
     Dim bAvailable As Boolean
     bVorhanden = False
    
     Set wrd = CreateObject ("Word.Application")
     wrd.Visible = True
     Set newDoc = 
wrd.Documents.Add (Template: = "S: \ XXX \ XXX \ XXX.dot")
     With new doc
        
     wrd.Selection.WholeStory 'Selects the whole document
     wrd.Selection.Fields.Update 'Updates all fields that have been entered
    
     'abc = wrd.CursorAufText

End With
wrd.Visible = True

    
End Sub

I would be happy to know if I could help.

Nikolino

I know I don't know anything (Socrates)

* Kindly Mark and Vote this reply if it helps please, as it will be beneficial to more Community members reading here.