I have a Word document with an OLE object linked to a spreadsheet.

I'd like to make this Word document a template and have my table auto-populate by referencing a spreadsheet in the "current directory" with a specific name (call it "test.xls" for now). "test.xls" would be located in many other directories, but each "test.xls" spreadsheet would be unique with different data - corresponding to the project. My goal is to copy the word template file into any one of those directories with the "test.xls" spreadsheet and have my document auto populate with the spreadsheet in the same directory.



Sounds simple, but word won't let me define this in the OLE options. It appears that the source file path must be defined to a specific (static) directory and can't just reference the current active directory.

Anyone have a work around? VBA perhaps?

Thanks,
Shaun