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

[Johnny C]

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"

    ' 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?

 

[Johnny C]

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.