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.
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.