Excel VBA - Checking externally linked files

[mandanarchi]

First off I apologize if this is in the wrong place - there isn't a section for Excel and I figured that Excel/Access VBA are similar enough for me to get away with it. Someone please move it if they think there's a more appropriate place for it.

To the problem.
A friend has asked me if I know of a way to keep external links unbroken - even if the linked workbook is moved or renamed. I don't think there's an easy way.
It's not possible for him to put all the worksheets into one workbook (I haven't seen the setup, I'm taking his word for it).

I thought that maybe he could put a hidden sheet in each linked file with a specific code in - something that would uniquely identify that book - and have a code in the main workbook to run on start-up that could check for broken links and if there are any, search for the unique codes in all excel files then auto-update the links when it's found.

Does that sound feasible, or does anyone else have a solution?
I was so adamant on Googling it that I forgot to eat my dinner (I'm at work)

If it does sound feasible, does anyone have any pointers on where I can start with the code? I've found plenty of looking for text in cells, but nothing about looking for text in specific places (or hidden sheets) in external files.

Thanks

[NeoPa]

This is fine for this forum Manda.

I have trouble understanding what you're looking for though. If a link is broken then what can be done automatically other than report it?

Keeping links intact seems to me more about designing the links to work using UNCs instead of drive letters to me. Looking in Excel files across your system, or even worse your network, seems quite unreasonable to me. A horrendous amount of work to process through. Even for a computer this could easily take inordinate amounts of time.

[mandanarchi]

Thanks NeoPa, that's what I thought too.

My first thought when he asked me about this was - if the workbooks are that important, why would they be moved or have their names changed?

I have no idea what these sheets are for, or what they contain, I just thought I'd ask, see if I was overlooking something obvious.
Thinking about it I don't even know if they're on a network share or not - though I guess they must be if multiple people use them.

I think he wants it to be able to auto-relink because the people using it aren't computer literate - maybe they'd link the wrong files, don't know how to relink etc.

Never mind, I'll let him know he'll just have to tell the others not to go changing things they don't understand.

Thanks

[NeoPa]

2 things :

1. Local drives can also be referred to using UNC. It's unusual, but perfectly feasible.
2. Such relinking is often caused by emailing the master document to someone.

Designing such documents has always been done since the availability has been there. It was never a great idea except in some very limited circumstances. Very powerful, mind you. Just always prone to serious and difficult to solve problems when anything changed. Later versions provide better support for relinking, but nevertheless still pretty hard to get right. All-in-all, something to avoid if possible. Just my opinion, but from a little experience with that issue.

[mandanarchi]

Thanks again NeoPa.

I'm more comfortable with Access to be completely honest. Though I'm quite happy with excel formulas, I try to avoid excel vba & macros where I can.

[NeoPa]

A pleasure Manda.

As far as Excel VBA is concerned, I found that much easier to get to grips with originally than Access VBA, as Excel has a Record Macro facility that converts what you do as an operator into VBA commands. It's not the best code you'll ever see, but it certainly gets you going and helps you to find your way around the place. If ever you decide you want to get into it, remember to use this approach to start with. A much easier learning curve ;)