How to Refer to Excel Link Using Unique Identifier in VBA

This is an alternate way of referencing links.

Dim linkName As String, fileName As String, i As Integer

For Each link In ActiveWorkbook.LinkSources
    On Error GoTo tryName
    ActiveWorkbook.UpdateLink linkName

    If False Then
  tryName:
        i = InStrRev(linkName, "\") ' 0 if no "\" found
        If i > 0 Then
            On Error Resume Next ' to ignore error if fileName does not work too
            fileName = Mid(linkName, i + 1)
            ActiveWorkbook.UpdateLink fileName 
        End If
    End If
    On Error GoTo 0 ' reset the error handling        
Next

However link as before is a string of the filepath

Update

Can you post a screenshot of the Data > Edit Links to make it a bit more clear?

In my tests the first 3 links were fine, but the last one had problems.

You could try something like below

1. Test if the link is from an open workbook
2. If it used, then use ChangeLink to fool Excel into making the update
3. If not, run the existing code that works on closed book.

code

 Sub updateValues()
 Dim updFilePath As String
 Dim Wb As Workbook
 Dim bFound As Boolean

 updFilePath = ActiveWorkbook.Sheets("INPUTS").Range(updCellRef).Value

 For Each Wb In Application.Workbooks
 If Wb.FullName = updFilePath Then
    ActiveWorkbook.ChangeLink Wb.Name, Wb.Name
    bfound = True
    Exit For
 End If
 Next

 If Not bfound Then ActiveWorkbook.UpdateLink Name:=updFilePath, Type:=xlExcelLinks
End Sub

I can think about 2 scenarios that you may have here:

1. By the title I can guess the problem relies in the fact that, the workbooks that you are trying to refer to are within a sub folder in the parent workbook; if so, I have noticed that even when you give the full path, it works for a time and then it miss leads the path for it -it seems this is a bug (I don't know what triggers it though)-. Links only works in excel interface but, when you are tying to play with the hyperlink in vba it gives error because the full path has been cut off and this leads to an incomplete path -hence to verify it, it says is not longer valid-. I have no other solution that, when this happens, ask the user again for the path (use a master cell for all the process that rely on this to make it easier to fix/workaround). This may solve it in order to retrieve it by VBA. Just make sure the cell value has the full name for the workbook when asking for it-

    Sub Test()
    Dim HLToTest As String
        HLToTest = RetriveWBLink(Range("B2").Value)
    End Sub
    Function RetriveWBLink(WBName As String) As String
    Dim FileSystemLibrary As Object: Set FileSystemLibrary = CreateObject("Scripting.FileSystemObject")
        On Error GoTo Err01RetriveWBLink
        RetriveWBLink = FileSystemLibrary.GetFile(ThisWorkbook.Path & "\" & WBName)
        If 1 = 2 Then '99. If error
Err01RetriveWBLink:
        'this may happen for new workbooks that aren't saved yet
        RetriveWBLink = "False"
        End If '99. If error
        On Error GoTo -1
        Set FileSystemLibrary = Nothing
    End Function

2. If (1) is not the case, this should solve it by retrieving the full path of the given WB (this is just going to update the link, doesn't matter if it's already open or not)

Sub Test()
Dim HLToTest As String
    HLToTest = RetriveWBLink(ThisWorkbook)
End Sub
Function RetriveWBLink(WBName As Workbook) As String
Dim FileSystemLibrary As New Scripting.FileSystemObject
    On Error GoTo Err01RetriveWBLink
    RetriveWBLink = FileSystemLibrary.GetFile(WBName.Path & "\" & WBName.Name)
    If 1 = 2 Then '99. If error
Err01RetriveWBLink:
    'this may happen for new workbooks that aren't saved yet
    RetriveWBLink = "False"
    End If '99. If error
    On Error GoTo -1
    Set FileSystemLibrary = Nothing
End Function

I'm not saying this is the only way, but the easiest way I can think of is to actually open the workbook using something like this:

Dim wb as Workbook
Set wb = Excel.Workbooks.Open(Filename)

updFilePath = wb.Sheets("INPUTS").Range(updCellRef).Value
wb.Close

I understand your point, that if the spreadsheet is the same name as your open spreadsheet, it will puke. Maybe a simple hack is to capture the filename of the active workbook, save it as a temp file, and then save it back at the very end. I did say it was a hack.

I know you can access Spreadsheet data like a database using ADO through C# or MS Access, so I'm guessing it's also possible to do this directly from Excel. That said, it hardly seems like less of a hack than the suggestion above. I think ADO also has to read the entire spreadsheet even to process a single cell, so I don't think this saves you anything anyway.