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
\.

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
oups.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.

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

oups.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

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

oups.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

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

I am getting a complie error that says 'method or data member not
found' on the VBA.UBound part of the code. Any Ideas how to fix?

Thanks,

Joshua

On May 9, 11:01 am, cass calculator wrote:
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

roups.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

Yes, there is a way how you can do this. It's actually using the
System.Object class within VBA. The System.Object class more or less allow
you to do the same stuff that you use Windows Explorer for, but only
programmatically. The FileSearch object in this case is just an extension
to the System.Object class. See the following:

Dim strFileName As String, lngFileNum as Long
With Application.FileSearch
.NewSearch
.LookIn = "C:\backups\modelcopy\backup\"
.SearchSubFolders = True
.FileType = msoFileTypeExcelWorkbooks
If .Execute() 0 Then
For lngFileNum = 1 to .FoundFiles.Count Step 1
strFileName = FoundFiles(lngFileNum)
<Run the other code with the "strFileName" variable in the name
argument of the workbook object
Next lngFileNum
Else
MsgBox "There were no Excel workbooks in the backup directory.", 48
End If
End With


"cass calculator" wrote in message
oups.com...
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

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

Looks like I got it to work by just using UBound(aLinks) instead of
VBA.UBound(aLinks). I'm still trying to figure out how to put this
code into a for loop to run for every excel file in the directory.
Any suggestions?

Thanks,

Joshua