Changing Links In Excel Causes Formulas To Change

[MDW]

I've got a workbook called sec_model.xls. This file contains a link to a file
called output.xls. This output.xls is created by a macro in another file.

I'm writing a macro to run several different scenarios in sec_model.xls, and
as part of that I programmatically change the link source. I know how to do
this. However, whenever I do that, the formulas that refer to the linked
sheet turn into #REF! errors.

Here is the code to change the link: ThisWorkbook.ChangeLink strOldInput,
strLoanOutput

For instance, a formula that says "='C:\[output.xls]loan cash flows'!C14"
will change to "='C:\[output.xls]#REF!'!C14" after I run the code.

I have tried running this code with the "output.xls" file open or closed,
and it doesn't make a difference. I can tell you that in 90% of cases,
strOldInput and strLoanOutput will be the same file (in other words, I'm
"changing" the link to be the exact same file it's already linked to).
However, I do that manually all the time and it doesn't cause a problem.

Any thoughts or ideas as to what the problem could be?

 

[Deborah Digby]

you could just create a routine like this whic updates the cell:
sub UpdateLink(strLoanOutput as String)
Range("A1").formula = "='C:\[" & StrLoanOutPut & "]loan cash flows'!C14"
end sub

and call it every time the file changes

 

[MDW]

Sorry, but that solution is not going to be practical for I have thousands of
cells with formulas that refer to the linked file.

I'm more curious as to why if I do something manually (Edit -> Links), it's
not a problem, but via code (ThisWorkbook.ChangeLink), I get the errors.
--
Hmm...they have the Internet on COMPUTERS now!

you could just create a routine like this whic updates the cell:
sub UpdateLink(strLoanOutput as String)
Range("A1").formula = "='C:\[" & StrLoanOutPut & "]loan cash flows'!C14"
end sub

and call it every time the file changes

I've got a workbook called sec_model.xls. This file contains a link to a file
called output.xls. This output.xls is created by a macro in another file.

I'm writing a macro to run several different scenarios in sec_model.xls, and
as part of that I programmatically change the link source. I know how to do
this. However, whenever I do that, the formulas that refer to the linked
sheet turn into #REF! errors.

Here is the code to change the link: ThisWorkbook.ChangeLink strOldInput,
strLoanOutput

For instance, a formula that says "='C:\[output.xls]loan cash flows'!C14"
will change to "='C:\[output.xls]#REF!'!C14" after I run the code.

I have tried running this code with the "output.xls" file open or closed,
and it doesn't make a difference. I can tell you that in 90% of cases,
strOldInput and strLoanOutput will be the same file (in other words, I'm
"changing" the link to be the exact same file it's already linked to).
However, I do that manually all the time and it doesn't cause a problem.

Any thoughts or ideas as to what the problem could be?

 

[MDW]

Nevermind, I figured out what the problem was.

I mentioned in my original post that ouput.xls was created by a macro? Well,
the macro was written by somebody else, and I just looked to see how they do
it. They delete the current file and then save a new copy. I assumed they
just overwrote was what there.

This other macro is run while I have sec_model.xls open, and so the link was
sensing that deletion.

Sorry for the confusion.
--
Hmm...they have the Internet on COMPUTERS now!

you could just create a routine like this whic updates the cell:
sub UpdateLink(strLoanOutput as String)
Range("A1").formula = "='C:\[" & StrLoanOutPut & "]loan cash flows'!C14"
end sub

and call it every time the file changes

I've got a workbook called sec_model.xls. This file contains a link to a file
called output.xls. This output.xls is created by a macro in another file.

I'm writing a macro to run several different scenarios in sec_model.xls, and
as part of that I programmatically change the link source. I know how to do
this. However, whenever I do that, the formulas that refer to the linked
sheet turn into #REF! errors.

Here is the code to change the link: ThisWorkbook.ChangeLink strOldInput,
strLoanOutput

For instance, a formula that says "='C:\[output.xls]loan cash flows'!C14"
will change to "='C:\[output.xls]#REF!'!C14" after I run the code.

I have tried running this code with the "output.xls" file open or closed,
and it doesn't make a difference. I can tell you that in 90% of cases,
strOldInput and strLoanOutput will be the same file (in other words, I'm
"changing" the link to be the exact same file it's already linked to).
However, I do that manually all the time and it doesn't cause a problem.

Any thoughts or ideas as to what the problem could be?