Changing Links In Excel Causes Formulas To Change
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!
"Deborah Digby" wrote:
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" wrote in message
...
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?
--
Hmm...they have the Internet on COMPUTERS now!
|