programatically change source of multiple links for multiple files

[cass calculator]

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

 

[Ronald Dodge]

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]

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

 

[Ronald Dodge]

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]

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

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

 

[cass calculator]

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

 

[cass calculator]

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

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

 

[Ronald Dodge]

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

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.

 

[Ronald Dodge]

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

 

[Ronald Dodge]

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