Forcing Stubborn Recalculation

Hi Allen, interesting tip. Been troubleshooting some workbooks with problems and this could be very useful.

However this is not our only problem.

We have an Access Database application that manages the workbooks. We have noticed a failure to update links to source no matter what flavour of the F9 Recalculate key is used.

In particular we find that a field pointing to a source that links to another source will not consistently update. We have also found that links to a source workbook work when the Excel workbook is opened from one part of the application but fails when called from another part of the application. Looking at the code I suspect it is due to the way it is being "called". I fairly certain that one part of the application uses a UNC path and the other uses a mapped drive.

Found this on MS

https://support.microsoft.com/en-gb/topic/description-of-link-management-and-storage-in-excel-46628e8d-2cd6-db5f-3474-f8d7144b09d6
(relevant Text from this below my signature)

Your thoughts on this would be greatly appreciated and could be the source of another tip.

Cheers
Nick

Mapped drives vs. UNC
When a source data workbook is linked, the link is established based on the way that the workbook was opened. If the workbook was opened over a mapped drive, the link is created by using a mapped drive. The link remains that way regardless of how the source data workbook is opened in the future. If the source data file is opened by a UNC path, the link does not revert to a mapped drive, even if a matching drive is available. If you have both UNC and mapped drive links in the same file, and the source files are open at the same time as the destination file, only those links that match the way the file was opened will react as hyperlink. Specifically, if you open the file through a mapped drive and change the values in the source file, only those links created to the mapped drive will update immediately.

The link displayed in Excel may appear differently depending on how the workbook was opened. The link may appear to match either the root UNC share or the root drive letter that was used to open the file.

Scenarios that may cause links to not work as expected
There are several circumstances in which links between files can be inadvertently made to point to erroneous locations. The following are two of the most common scenarios.

Scenario 1:

You map a drive under the root of a share. For example, you map drive Z to \\Server\Share\Folder1.

You create links to a workbook that is stored at the mapped location after you open the file through that mapped drive.

You open the file by a UNC path.

As a consequence the link will be broken.

If you close the file without saving it, the links will not be changed. However, if you save the file before you close it, you will save the links with the current broken path. The folders between the root of the share and the mapped folder will be left out of the path. In the example above, the link would change to \\Server\Folder1. In other words, the Share name is eliminated from the path.

Scenario 2:

You map a drive under the root of a share. For example, you map drive Z to \\Server\Share\Folder1.

You open the file by a UNC path or a mapped drive mapped to a different folder on the share, such as \\Server\Share\Folder2.

As a consequence, the link will be broken.

If you close the file without saving it, the links will not be changed. However, if you save the file before you close it, you will save the links with the current broken path. The folders between the root of the share and the mapped folder will be left out of the path. In the example above, the link would change to \\Server\Folder1.