Need code for the following situation:

Person A sends spreadsheet to Person B. Person B does NOT
need to use links. Person B makes changes and sends
spreadsheet back to Person A. Person A then needs to have
the original links restored.

I know that Excel 2002 has an option for breaking links.
Don't know if this is what I need. At any rate, I don't
myself have 2002 and therefore can't create a macro to
find out the language.

Any suggestions will be greatly appreciated!

Sandy

Sandy,

maybe a lot of work once a book is ready,
but worth a try

to manage external links...

activate cel A1, then for each sheet in
external file define a NAME like

extData1 ='[x:\folder\file.xls]sheet1'!A1
extData2 ='[x:\folder\file.xls]sheet2'!A1

NOTE the names are RELATIVE references, that's
why you MUST enter them from cell A1)

NOW in formulas point to the name instead of
directly to the external file (search & replace).

now you can manage the names (probably no more then 5)
instead of the filelinks.

you might do this with code but folliwing is easier:
in the 'receiving' book use sheet1!c2 as a "toggle"
(0=dont use links,1=use links)

then redefine the above names to following
(again remember to activate cell a1 first)

extData1 =if(sheet1!$c$2=0,0,'[x:\folder\file.xls]sheet1'!A1)
etc..



TIP:

you can alse have a look at following addins:

FindLink (Bill Manville)
FlexFind (Jan Karel Pieterse)

both from http://www.bmsltd.co.uk/MVP/MVPPage.asp



keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


"Sandy" wrote:

Need code for the following situation:

Person A sends spreadsheet to Person B. Person B does NOT
need to use links. Person B makes changes and sends
spreadsheet back to Person A. Person A then needs to have
the original links restored.

I know that Excel 2002 has an option for breaking links.
Don't know if this is what I need. At any rate, I don't
myself have 2002 and therefore can't create a macro to
find out the language.

Any suggestions will be greatly appreciated!

Sandy

Thanks for your reply!

Well, I followed the logic until the part about putting
something in the "receiving book." What do you mean
by "receiving book"?

Also, how do you set up a "toggle" that tells Excel that 0
means don't use links and 1 means to use links?

Thanks again!

Sandy

-----Original Message-----
Sandy,

maybe a lot of work once a book is ready,
but worth a try

to manage external links...

activate cel A1, then for each sheet in
external file define a NAME like

extData1 ='[x:\folder\file.xls]sheet1'!A1
extData2 ='[x:\folder\file.xls]sheet2'!A1

NOTE the names are RELATIVE references, that's
why you MUST enter them from cell A1)

NOW in formulas point to the name instead of
directly to the external file (search & replace).

now you can manage the names (probably no more then 5)
instead of the filelinks.

you might do this with code but folliwing is easier:
in the 'receiving' book use sheet1!c2 as a "toggle"
(0=dont use links,1=use links)

then redefine the above names to following
(again remember to activate cell a1 first)

extData1 =if(sheet1!$c$2=0,0,'[x:\folder\file.xls]sheet1'!
A1)
etc..



TIP:

you can alse have a look at following addins:

FindLink (Bill Manville)
FlexFind (Jan Karel Pieterse)

both from http://www.bmsltd.co.uk/MVP/MVPPage.asp



keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


"Sandy" wrote:

Need code for the following situation:

Person A sends spreadsheet to Person B. Person B does
NOT
need to use links. Person B makes changes and sends
spreadsheet back to Person A. Person A then needs to
have
the original links restored.

I know that Excel 2002 has an option for breaking
links.
Don't know if this is what I need. At any rate, I
don't
myself have 2002 and therefore can't create a macro to
find out the language.

Any suggestions will be greatly appreciated!

Sandy



.