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