Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Changing Links In Excel Causes Formulas To Change
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! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Changing Links In Excel Causes Formulas To Change
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! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Changing Links In Excel Causes Formulas To Change
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" 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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Edit Links: Changing links on a protected worksheet | Excel Discussion (Misc queries) | |||
change file links or updating formulas | Excel Programming | |||
Links in formulas change when another user runs a workbook - 2003 | Excel Programming | |||
Links in formulas change when another user runs a workbook | Excel Discussion (Misc queries) | |||
Changing Links in Excel with VBA | Excel Programming |