how can I fix Broken Links

[Chris Wilkinson]

hi,

I have a fairly complex application with one workbook as the controlling
one. All the code is in this workbook. Even the code for buttons in other
workbooks. I tested it and it worked fine. But when I gave it to a user to
test it kept sayin it wanted to open a new version of the controlling
workbook. When I looked into this, I discovered that the button was looking
for the macro in the wrong place. Also, there are defined named ranges that
have the same problem. I can get these working again but physically going to
edit>links and chaging the source. However, when I try using
thisworkbook.changelink, nothing happens. The links stay broken. The problem
is that to change a link this way, it is necessary to know the name of the
link (with the original path) in advance. Is there anyway of list all the
links in a workbook? that way I could search for the broken link and fix it.
If there is a better way to fix broken link in code, I would be grateful if
someon would enlighten me. Otherwise I am going to have to go into edit>links
on each PC that I have to install the application on.

Thanks,
Chris

 

[Dave Peterson]

Those buttons are assigned to a specific workbook.

One way to fix this is to make sure that the buttons point to the correct
workbook each time you open the workbook. You'll have to loop through them and
change the .onaction procedure names to point to the correct location/workbook.

But maybe you can do something else.

If these buttons are the equivalent of a toolbar (or are on a toolbar), you can
have your workbook (addin?) with the code create a toolbar or add another item
to the worksheet toolbar that allows the users access to your macro.

The addin can build the toolbar each time it opens and it'll destroy it each
time the addin closes.

For additions to the worksheet menu bar, I really like the way John Walkenbach
does it in his menumaker workbook:
http://j-walk.com/ss/excel/tips/tip53.htm

Here's how I do it when I want a toolbar:
http://www.contextures.com/xlToolbar02.html
(from Debra Dalgleish's site)

 

[Chris Wilkinson]

Dave,

I had already worked out how to update the buttons, in the same way you
suggest, but it is other links that are a problem too. Is there no Links
collection for a worksheets and/or workbooks? It there is I have missed it.
When I recorded the macro which fixed the links, I got:-
ChDir "C:\test"
ActiveWorkbook.ChangeLink Name:= _
"C:\test\test\Book2.xls", NewName _
:="C:\test\Book2.xls", Type:= _
xlExcelLinks

The problem with fixing the link is that you need to know the original
linkname with full path before your can change it to the new path. after
looking on the net for a solution to this and only finding commercial ones, I
am beginning to think it is not that straightforward. I think I'll have to
loop through the names collection, any controls and any cell references.

Thanks anyway,
Chris

 

[Dave Peterson]

Links can hide in some strange places.

After you finish doing all you can think of, get Bill Manville's FindLink
program:
http://www.oaltd.co.uk/MVP/Default.htm

to see if you got them all.

And if you look at VBA's help for .linksources, you'll see a way to get all(?)
of them (so you can loop through them).