Absolute paths in formulae for linked workbooks

[welshgasman]

Hi all,
I have created an Excel workbook that is a summary of other Excel files.

In each of the data files there is a summary sheet and I have referred to each summary sheet cell with the formula
='\\SRVPC\EFS-Shared\JAGB\JAG\[2017 GMP Stats.xlsx]SIPP'!E2

The above is in cell E2 of the summary workbook and the formula is repeated. I have a 2016 workbook and now starting the 2018 workbook.

All files are in the folder ='\\SRVPC\EFS-Shared\JAGB\JAG\ and when I open the summary file in that folder, all works as it should.

However to stop the managers altering the workbook, I copy the workbook to their temp folder on their PC and then open it via a batch file. I was doing this with the data file for last year, but now we are on the third year I decided to create this summary sheet, as all they are interested in is the summary sheet in each data file.

When the file is opened on a user's pc instead of the correct path showing in the formula, the path above is replaced by the temp path C:\Temp and of course the files are not located there, so I get the broken links message.

How can I stop this happening and persuade Excel to use the original path.? That is why I used the UNC path and not the mapped drive as they could be different for each user.
From this link, it appears they think absolute paths are a no no, but in this situation this is what I need?

TIA

 

[welshgasman]

Always the way. You post and then you find something else to try.

I have copied the file to the temp location and changed the source. then I changed the mapped path for the UNC path and saved the file and copied back to original location.
Now the batch file copies it and the links stay as they are for now, but I have seen mention that if I have to edit the file I will lose the links and have to repeat.

Will have to see how it goes.