Find and change external links to point to the same workbook instead

[Maria J-son]

Hi,

I have a workbook with a lot of data and a lot of code into which I make
release updates. Therefore, I often need to copy 'n paste the data from the
old workbook into the sheets of the new release.

When I have a workbook named wb1.xls and into that file links between
sheets, the links become links still pointing at that named wb1.xls when I
paste the data into a new woorkbook named wb2.xls.

If a link in the old wb1.xls was "=Sheet2!A1" it will become
"=[wb1.xls]Sheet2!A1" in the new wb2.xls file, when I pasted the data into
the new release. I still need to have the link to be "=Sheet2!A1".

Please tell me how I with a macro into all sheets can search, find and
eliminate these links and continue to link to the Sheet1 into the same
workbook.


/Kind regards

 

[mattytee123]

Maria,

Use the edit > replace function and replace the previous file name with
the current file name, ie replace 'wb1.xls' with 'wb2.xls' and this
should link to the current workbook.

Mattytee.

 

[Maria J-son]

Sorry, it's no good. I need a macro that find and deal with all external
links, no matter what the names are. Code built as an ordinary search n'
replace is only using one unique link - I need a generic macro that
recognise an external link and eliminate it.

/regards

 

[Dave Peterson]

How about fixing the problem so it doesn't occur?

If yes...

Then one way around it is to convert the formulas to text, copy the text, and
reconvert the text back to formulas:

Select that range to copy (ctrl-a (twice in xl2003) will select the whole sheet)
Edit|replace
what: = (equal sign)
with: $$$$$= (some unique string)
replace all

Now those formulas are just text.

Copy and paste

And then
edit|replace
what: $$$$$=
with: = (some unique string)
replace all

Both in the original worksheet and the pasted worksheet.

Hi,

I have a workbook with a lot of data and a lot of code into which I make
release updates. Therefore, I often need to copy 'n paste the data from the
old workbook into the sheets of the new release.

When I have a workbook named wb1.xls and into that file links between
sheets, the links become links still pointing at that named wb1.xls when I
paste the data into a new woorkbook named wb2.xls.

If a link in the old wb1.xls was "=Sheet2!A1" it will become
"=[wb1.xls]Sheet2!A1" in the new wb2.xls file, when I pasted the data into
the new release. I still need to have the link to be "=Sheet2!A1".

Please tell me how I with a macro into all sheets can search, find and
eliminate these links and continue to link to the Sheet1 into the same
workbook.

/Kind regards