Changing File Names and Linking Cells between workbooks

[Guest]

Hello,

I have one master Workbook where I am bringing worksheets in from other
Workbooks. Some of the cells are linked with Name, Date, and Project Number
which resides in the master workbook and must transfer when the worksheet is
brought in.. My Problem is: My master workbook can change names which
affects the linked data file name in the worksheets I am bringing in. The
code in the linked cell reads “ ='Z:\Project Forms XLS\[My Project
Template.xls]Menu'!$E$5 “ but my file name can change. Can I use a variable
or something in my linked data to accommodate changing master worksheet file
names?

 

[Guest]

James:

try it,


ActiveWorkbook.ChangeLink "Z:\Project Forms XLS\My Project Template.xls", _
"Z:\Project Forms XLS\My Project Template-1.xls", xlExcelLinks