How to Change External Link to Internal Link

[Guest]

Excel 2003. I am programmatically copying selected sheets from one workbook
to another. For example, I copy four sheets from one workbook to another.
The fourth sheet copied has formulas that reference the first three sheets
copied. When the fourth sheet is copied, an external link is created in
these formulas that references the "copy from" workbook. I want the fourth
sheet to reference the sheets in the "copy to" workbook rather than the "copy
from" workbook. How may I accomplish this? Thanks for any help.

 

[Franz Verga]

Excel 2003. I am programmatically copying selected sheets from one
workbook to another. For example, I copy four sheets from one
workbook to another. The fourth sheet copied has formulas that
reference the first three sheets copied. When the fourth sheet is
copied, an external link is created in these formulas that references
the "copy from" workbook. I want the fourth sheet to reference the
sheets in the "copy to" workbook rather than the "copy from"
workbook. How may I accomplish this? Thanks for any help.

Hi Doug,

I don't if it is possible from a macro, but from menu Edit, Links, you have
the possibility to change the source of a link from a workbook to another,
even to the workbook in which the link is.

You could try to record a macro when you do the operations...

--
(I'm not sure of names of menus, options and commands, because
translating from the Italian version of Excel...)

Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy

 

[tausif.mohammed]

Excel 2003. I am programmatically copying selected sheets from one workbook
to another. For example, I copy four sheets from one workbook to another.
The fourth sheet copied has formulas that reference the first three sheets
copied. When the fourth sheet is copied, an external link is created in
these formulas that references the "copy from" workbook. I want the fourth
sheet to reference the sheets in the "copy to" workbook rather than the "copy
from" workbook. How may I accomplish this? Thanks for any help.

Hi Chaplain.

Try using changelinks method ... eg
ActiveWorkbook.ChangeLink "c:\excel\book1.xls", "c:\excel\book2.xls",
xlExcelLinks


Please check the help file for additional info ..

Cheers,
Tausif

 

[Guest]

Thank uyou for the tip. That is what I used.