programatically change source of multiple links for multiple files
 

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

programatically change source of multiple links for multiple files
 

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

programatically change source of multiple links for multiple files
 

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

programatically change source of multiple links for multiple files
 

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

programatically change source of multiple links for multiple files
 

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

programatically change source of multiple links for multiple files
 

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

programatically change source of multiple links for multiple files
 

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

programatically change source of multiple links for multiple files
 

To address this compile issue, check to be sure the following is checkmarked
as references on the VBA side by going to ToolsReferences

Visual Basic For Applicaitons
Microsoft Excel x.0 Object Library
Microsoft Office x.0 Object Library
Microsoft Visual Basic For Applications Extensibility 5.3 (the version
number may be different for other versions, which I'm using Excel 2002)

The reason why I prequalify my stuff is to prevent any sort of naming
conflicts or reference errors.

On some computers, I have ran into the issue of VBA methods not working
without having the "VBA." in front of the method name.

About the only exception to this rule is dealing with the data type
conversion functions or other VBA structure type codes that I have not used
the object's code name qualification in front of the method's name.

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

programatically change source of multiple links for multiple files
 

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

programatically change source of multiple links for multiple files
 

Sorry for the delayed response as I was out for a few days dealing with a
new addition to my family.

Sincerely,

Ronald R. Dodge, Jr.
Master MOUS 2000

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