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



.

KeepITCool:

Thanks so much for your response.

Haven't had a chance to try your solution out. They have
me on another part of the project for the moment . . . then
I'll be back to this part. I think I understand what you
mean. Thanks for your additional explanation.

Will email you if I can't get it to work.

Thanks again!

Sandy


-----Original Message-----
Sandy.

sorry for late response..been out.


'the receiving' book is my way of saying:
the book that contains the links, e.g. the summary book,
not the book containing the data that is being linked.

clear so far?

the toggle is just a cell,
which either contains the value 0 or 1.
and thus acts like a toggle (on/off yes/no)

situation old:
formulas pointing to databook.

situation intermediate:
formulas pointing to names.
names pointing to databook

situation final:
formulas pointing to names
depending on content of cell (e.g. C2)
the names are either pointing to 0 or to databook.
e.g.
extData1 =if(sheet1!$c$2=0,0,'[x:\folder\file.xls]sheet1'!
A1)


for the users NOT interested or allowed the link,
you set the cell C2 to 0.

if you want to work with the links you set the cell's
value to 1 (or at least < 0)


pls let me know if it's clear.
else feel free to email a workbook (ZIPPED!)
to address below and I'll work on it tomorrow.



keepITcool

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


"Sandy" wrote:

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



.



.