Thread: Link between two files where one file name is dynamic

I have two excel files that I am linking, where one file always keeps the same file name (File 1.xls) and another file that is opened as read only and then saved as a different name (File2.xls).

So File1.xls is from a supplier and is consistently updated and calculates certain results, File2.xls is a template for quoting where customer specific information is entered, and calculated results from File1.xls are displayed.

File2.xls is then used a 'Save As...' and the file name is changed. What I'm trying to do is write a macro to re-link File1.xls and the new NewName2.xls file. Here's what I have so far:

Code:

Sub Macro1()

Windows("File1.xls").Activate
Application.Run "File1.xls!UnProtectInputsSheet"
ChDir "C:\Customer Files\"
ActiveWorkbook.ChangeLink Name:="File2.xls" _
, NewName:="FileToOpen = Application.GetOpenFilename _", Type:=xlExcelLinks
End Sub

So an open file dialog opens, the new renamed file is selected, but the new source file becomes

Code:

 "FileToOpen = Application.GetOpenFilename _"

and any changes that are made after that are not reflected in File1.xls.

Obviously I'm a newb at this, but I'm trying to figure out to have an FileToOpen function work with a dynamic file name change. Any suggestions would be greatly appreciated and happy I found such a great message board!