Links updating in VBA incorrectly to values previously overwritten???

Johnny C

Well-known Member
Joined
Nov 7, 2006
Messages
1,008
I've got 2 files, one with a sheet linked to the other so that they are the same sheet.
The sheet linking to the other one is "Forecast - 8 - Output - 2020-23.xlsb"
On sheet Constants, the formula in cell M22 is
='[Forecast - 1 - Assumptions - 2020-23.xlsb]Constants'!M22
That file is open. The value is the same, 50.277
Both files are saved.

Last year, that cell had 48.184 in. it got updated 2 months ago. It's just a hard-typed number.
I have some VBA to update a load of linked files, 2 of which are the above. They need to be opened in a certain order, and neither must update any other links other than those between the 2 files.

Before this runs, both files have 50.277 in.

strLinkedFile = "Forecast - 8 - Output - 2020-23.xlsb"
strLinkedFileName = "Forecast - 1 - Assumptions - 2020-23.xlsb"

Code:
    ' Open file with links but don't update links
Workbooks.Open strFolder & strFile, UpdateLinks:=False
...
Workbooks.Open strLinkedFile, UpdateLinks:=False
Workbooks(strLinkedFileName).Close (False)                    ' Don't save changes in Linked file
Workbooks(strFile).Close (True)                                      ' Save changes

When this runs, and I open the file "Forecast - 8 - Output - 2020-23.xlsb" (and links not updated) the value in cell M22 has reverted to 48.184 - the value that was in last year. if I double click to open the source, it updates to 50.277

WTF is going on?
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

Johnny C

Well-known Member
Joined
Nov 7, 2006
Messages
1,008
Sorted.

It's another odd bug. Or it may be my misunderstanding, though I don’t think so as it’s not consistent.

I have a load of spreadsheet, which are linked in 1 direction.
File A has a value typed into a cell M22.
File B links to the typed cell M22 in file A
File C links to typed cell M22 in File A and other typed values in File B

File B seems to have some cached memory of a previous value for cell M22 in File A, and when File C opens File B without updating links, the value in File C becomes the ‘remembered’ value for File A in File B when an Application.Calculate is done even though File A is not open and File A has the new value in

There are about 30 pairs of linked files
File F links to File A and about 8 others which have been opened and the links have updated.

All files are opened in VBA with UpdateLinks:=False

If I have the value in Options>Advanced>When calculating this workbook>Update links to other documents checked (True)
The value in File F M22 (which is linked to M22 in File A) changes values even though File A is no longer open.
The value it is changing to is what was typed into File A M22 last year. It was updated 2 months ago,
1. When File F opens File A it updates correctly
2. When it opens files B it changes to last year’s value.
3. When it opens file C is reverts to the correct value.
4. When it opens file E it changes to last year’s values again.

It does this every time the macro is run. I have tracked it with both Debug.Print and a Watch set to the value in cell M22 in File F which breaks when the value changes.

If I have the value in Options>Advanced>When calculating this workbook>Update links to other documents unchecked (False), it changes correctly and the updated value sticks correctly.

Unfortunately that’s not a sticky setting, if I uncheck it and save it and close then reopen again, it’s enabled. The open way round it is to have a Workbook_Open event in ThisWorkbook to turn it off.
 

Forum statistics

Threads
1,136,864
Messages
5,678,212
Members
419,751
Latest member
richkings

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top