On May 8, 5:31 pm, "Ronald Dodge" wrote:
Finally, I find how to get to this information. It took quite a bit of
digging as it's not so readily spotted like most other things. You would
think you would use a collection object to get to the links, but it's not.
You actually have to use the "LinkSources" method on the workbook object to
get the array of links within the workbook.

Dim I as long, aLinks as Variant, strOldLink, strNewLink, strOldPath,
strNewPath, wbk as Workbook

'Change workbook name to name that is the workbook itself that's having the
links modified.

Set wbk = Workbooks("two.xls")
strOldPath = "C:\team\model\"
strNewPath = "C:\backups\modelcopy\backup\"
aLinks = wbk.LinkSources(xlExcelLinks)
If Not IsEmpty(aLinks) Then
For I = 1 to VBA.UBound(aLinks) Step 1
strOldLink = aLinks(I)
strNewLink = VBA.Replace(strOldLink, strOldPath, strNewPath)
If strOldLink < strNewLink Then
wbk.ChangeLink strOldLink, strNewLink, xlExcelLinks
End If
Next I
End If

Ronald R. Dodge, Jr.
Master MOUS 2000

"cass calculator" wrote in message

oups.com...

Thank you for your help.

1. Each of these files has multiple links to mulitple files. Your
code appears to just fix a specific link within a specific file. It
appears I would have to run this once for every link within a given
file, and repeat that for every file that contains external links.

How do I change the location of the source files for all links for all
files within the directory C:\team\model to C:\backups\modelcopy\backup
\. The names of the source files do not change, just the directory
where they are located.

2. How would I add a condition that states if the source file does
not exist in the new directory to keep the old source location. (I
have some links that are outside of the parent folder C:\team\model,
and I want those links to keep their existing source location.

Thanks,

Joshua

On May 7, 5:38 pm, "Ronald Dodge" wrote:
Try the following code line:

'Change the workbook name to the appropriate name or change the workbook
object to the appropriate workbook object.
Workbooks("two.xls").ChangeLink "C:\team\model\master.xls",
"C:\backups\modelcopy\backup\master.xls", xlExcelLinks

Ronald R. Dodge, Jr.
Master MOUS 2000

"cass calculator" wrote in message

groups.com...

I have a directory called "model" located at C:\team\model. Inside C:
\team\model\ is a file called "master.xls" , and the following
folders:

One
Two
Three
Four
Five
Six
Seven
Eight

The folder named "Six" contains a file called other.xls and 3 folders
with one or more .xls files within each of those three folders.
These .xls files link to master.xls and to other .xls files within the
folders "one", "two", "three", etc.

All of the other folders (one, two three, four, five, seven and eight)
contain 1 or more .xls files (most have more than 1), that link to
master.xls and that link to other .xls files located within the
folders "one", "two", "three", etc.

I would like to make a copy of everything inside C:\team\model\ and
put it into C:\backups\modelcopy\backup\.

When I do that, the "master.xls" links get updated to appropriately
refer to the files within the new directory I have created called C:
\backups\modelcopy\backup\. However, the files located inside the
folders named "one", "two", "three", etc. still link to C:\team\model
\.

Instead of going into each file and manually changing the sources of
all the links, how do I get all of the links re-sourced to refer to
the new directory I have created. The names of the folders and files
within the folders remain exactly the same. The only thing that has
changed is the parent directory from C:\team\model\ to C:\backups
\modelcopy\backup\.

I have been trying to solve this problem with no success. I greatly
appreciate anyone who can offer a solution!

Thank You,

Joshua


Thank you for your help. You are the man.

So I have to run this macro seperately for every file that contains
links I wish to modify, correct? Is there a way to run this macro for
all files in the new directory C:\backups\modelcopy\backup\?

Thanks,

Joshua