Link between two files with changing file name

[scir2008]

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:

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 "FileToOpen = Application.GetOpenFilename _" and any changes that 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 filename change. Any suggestions would be greatly appreciated and GLAD I found such a great message board!

 

[tafigueroa]

Not sure if you've gotten an answer to this, but here is your solution...change your code to:

Sub Macro1()

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