VBA to change links to external workbooks

[Jose_Chasez]

I have one workbook ("Master") that I am using to create a handful of new workbooks ("Templates"). When creating the new workbooks, there are a couple of worksheets that I copy from the "Master" file to the new files, as they contain data that is needed in the new files.
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
For example, I have a sheet called "Project Master List" that is copied from the master file and inserted into each template. Within the template, I have formulas that refer back to this "Project Master List" worksheet. My issue is that, within the newly-created workbooks, these formulas are referring to the "Project Master List" worksheet from the "Master" file, rather than the one in the newly-created workbook.
<o></o>
In other words, within the new workbooks, if I click on the formula bar for a given formula, I see:

='[Master File.xls]Project Master List'!$A$1

, instead of simply

='Project Master List'!$A$1

<o></o>
<o></o>
My question is how to remove these external links, and make my new files "self-contained". One additional wrinkle is that the name of the master workbook changes from time-to-time, so I'd prefer not to hardcode its name in the macro.
<o></o>
Thanks.

 

[Jose_Chasez]

Anyone? Not sure if I've clearly explained my issue above. Let me know if any additional information is needed to troubleshoot this.

Thanks again!

 

[Richard Schollar]

Hi Jose

Record a macro of yourself editing links>Change Source>Masterfile to the current file (ie refer back to itself).

This should give you the code you need.

 

[Jose_Chasez]

Thanks for the tip. The macro recorder was helpful. I only needed to tweak the code, so that it didn't refer to hard-coded file names and paths.

In case it helps anyone else, my final code is below.

• Folder_name is a string variable for the folder that the master file is saved in
• Source_WB is a variable for the "master" workbook that creates the new files
• Target_WB_Filename is a variable for the name of the new workbook being created by the macro

    ActiveWorkbook.ChangeLink Name:= _
            Folder_Name & Source_WB.Name, NewName:= _
            Target_WB_Filename, Type:=xlExcelLinks