Excel 2010 link updates return to old value

[accjrf]

I have an excel workbook that is linked to a second workbook. I also have code written in the first workbook that opens a 3rd workbook, copies comments from a textbox, and then pastes them into the first workbook. Here is my problem. In 2007 this works perfect but in 2010 when the code is executed and this line runs...

Set CMWkBk = Workbooks.Open("L:filename.xlsx", 0, ReadOnly)

The links that already visually updated return back to an old, incorrect value. After that, I cannot even manually refresh the links. Again, if I run the same thing in 2007, I have no problems. The links visually refresh and stay as the rest of my code runs.

This is an application that I have many people that use in our company and have a mix of 2007 and 2010 users so I can't fix it by downgrading users.

Any thought/help would be greatly appreciated!

 

[SkipVought]

him

This Set CMWkBk = Workbooks.Open("L:filename.xlsx", 0, ReadOnly) has nothing to do with links!

If these links are in formulas, then the formulas must be changed. You could use Find/Replace to do that.

I would have coded an Named Range with the link Path/Filename. Then only one simple change when something like that occurs. HOWEVER, to direct link cells or ranges is not a particularly sound best and accepted practice.



Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[accjrf]

Skip,

I know that line of code should have nothing to do with any links, however, it is very clear that when that line is executed by stepping through the code, the link that was updated gets reset to the original value. BUT this is only happening in 2010. The links do not have formulas...they are just fed from another sheet.