Excel documents stored on network drive, for example,
Data workbook.xls contains tables with data entered into. Report.xls contains UNC paths pointing to \\networkdata\excel\shared\workbook.xls
Users enter data into data workbook.xls, and then see this reflected in report.xls- one workbook pulls data from the other.
One machine is giving issues. If this machine opens, edits and saves these documents, it changes the paths to reflect the local machine!
The machine has the path mapped as a drive, eg H:\ (\\networkdata\excel\shared), so opening a file from here will give the path H:\data workbook.xls. I came across this article online which seems to talk about a similar issue-
The Knowledge Base article indicates that if a workbook is opened from a mapped drive, and the UNC >refers to that same drive, the UNC in the link is updated to the mapped drive designation.
Thinking that I had struck gold, I compared the set up (mapped drives, shortcuts etc) to another user's machine to identify a difference. The other machine has no issues but has the network location mapped exactly the same, as the same drive letter.
As a result this machine cannot be used to edit these shared workbooks, as if it saves it, it no longer points to the correct workbook on the network (eg it won't point to \\networkdata\excel\shared, but instread will refer to the location machine)