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!

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" <(E-Mail Removed)> wrote in message
news:4694714D-91AD-4621-935F-(E-Mail Removed)...
> 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!

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" <(E-Mail Removed)> wrote in message
> news:4694714D-91AD-4621-935F-(E-Mail Removed)...
> > 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!
>
>
>

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!


"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" <(E-Mail Removed)> wrote in message
> news:4694714D-91AD-4621-935F-(E-Mail Removed)...
> > 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!
>
>
>