Data Link Issue

[Simont485]

HI all

I have setup vba code to get comments from another workbook. It works fine until I close down all workbooks and reopen.

Then then link has changed from

=GetComments('[EPN - SPN - ROTA 1st April 2019 to 1st April 2020.xlsx]Rota 2019-20'!J35)

to

=GetComments('\\Ukpnforfs01\1$\Power Networks\Asset Management\NETWORK_OPS\NET_CNTRL\Control Room Rota\EPN SPN Control Room Rotas\[EPN - SPN - ROTA 1st April 2019 to 1st April 2020.xlsx]Rota 2019-20'!J35)

and the links are corrupted! I then have to re-make the links


VBA code
Function GetComments(pRng As Range) As String
'Updateby20140509
If Not pRng.Comment Is Nothing Then
GetComments = pRng.Comment.Text
End If
End Function

Bothe workbooks are stored the same file location

 

[bobsan42]

the links are not corrupted - they will work flawlessly without re-working them while the two workbooks are opened at the same time.

When the second Wb is opened the formula only contains it's name (it all that's needed at this point) and you can fetch the range properties.
When you close the second wb the links change to store its actual location. Which is perfectly normal. But at this point you cannot get to the range.
Think of it as a room inside a house - you can't enter the room without entering the house.
well you can sort of, but it's slightly more complicated.