You could try <bg.

I think you'd need an application event that looks for links when you're saving
any (and all) workbooks. Chip Pearson has some instructions about using
application events he

http://www.cpearson.com/excel/AppEvent.htm

But it still sounds scary to me.

If I have wkbkA open (happily making changes to the data) and your code tries to
open the file to update it, then you'll have trouble.

If you (or the users) decide that macros shouldn't be enabled--or that events
should be turned off, then there's trouble.

You're not going to like my response, but here goes.

I think one person should be responsible for making layout changes to those data
workbooks. Anyone can update (if you want), but no one can move columns around
or insert/delete columns. (Formulas in those receiving workbooks have to be
more robust, too--like Mike H. suggested.)

Then if that data workbook has to be redesigned, then one person makes the
changes. And updates the other workbooks--or at least informs or helps the
others with their updates. The timing would have to be published, too.

On June 30, 2008, workbookA.xls is changing its format. Existings columns J:K
will now hold xxx and yyy. There will be new data added in columns L:M. There
is a test workbook in zzzzzz that you can use to make sure your files will
retrieve the correct information.

Please contact Spencer Sadkin at xxxx if you have questions or help modifying
your workbooks.



wrote:

Dave,

Let me clarify.

Assume two workbooks wkbkA and wkbkB.

wkbkA contains data, and wkbkB is just created. You Decide that wkbkb
Cell C7 should contain a link to wkbkA. You go to Save wkbkB and your
add-in (on save) runs. the macro writes to wkbkA and creates a tab
that is hidden and contains a note that says path\wkbkB.xls'sheet1!
c7 then everything saves and wkbkA is closed (you never see that
wkbkA was open (in fact it shouldnt be opened just directly written
to))... then if someone opens wkbkA and decides to insert a new column
shifting column c to the right and becoming column d this would screw
up your wkbkB if you updated links. to prevent this when the user
goes to save wkbkA the macro runs and reads the hidden tab and will
alert the user that path\wkbkB.xls'sheet1!c7 will be affected. Then it
will prompt the user to cancel the save or to edit the link for them.

Mike,

I like the idea of a macro that writes the source down but thats also
why I like a true link, the link is the source.

--

Dave Peterson