How to Refer to Excel Link Using Unique Identifier in VBA

Question

How do I refer to external workbooks in VBA using a unique identifier which doesn't change when the file is open? It works fine when the full filepath is included and no file with the same name is open. However when a file is open the complete form with the filepath does not work and the filename alone does not work.

I wanted to create an update Sub to update all the references and this mucks itself up if the spreadsheet is open (refer to point 2 below).

These are some reasons why I feel it should be possible:

1. It seems that in the manual link update menu there is only the filename to refer to;
2. Also one can't open two workbooks with the same name, and thus if you open a source link then the cell references change from a file path to a file name (and it is this very thing which is causing an issue.

This is the code I currently has the updCellRef is a cell reference to the file-path (where I just want to use the file-name):

    Sub updateValues(updCellRef)
        updFilePath = ActiveWorkbook.Sheets("INPUTS").Range(updCellRef).Value
        ActiveWorkbook.updateLink Name:=updFilePath, Type:=xlExcelLinks
    End Sub

To clarify this problem arose when I was using the above function to update values however when the source spreadsheet was open it is referenced by its file name alone. When it is closed it is referenced by its full file-path.

I'm using Excel Professional 2010 v14 with VBA v7.0

Note: I don't want to use any other software including Power Query as it can't be installed without admin rights.

Answer 1

There are two ways to add info to the filename to make it unique is either to open the file in Excel where it is seen to that no open files are sharing the same name, or to include the entire path. So you can not "refer to external workbooks in VBA using just the filename" unless they are open since there would then be uncertainty to which of all files sharing the same names you are refering to.

Here is the source at MS Office Support saying that "When the source is not open, the external reference includes the entire path"

Update: given the comments to the original question, I guess we are here:

1. We are happy with the open files and any link to them which should be already updated since they are open
2. We have a list of files which we like to force update to if we can find them through given paths and if there is not another file with the same filename open

Now try this:

 Sub updateValues(updFilepath As String)
    If Not FileInUse(updFilepath) Then
        ActiveWorkbook.UpdateLink Name:=updFilepath, Type:=xlExcelLinks
    'else workbook is open and Excel have automatically updated linke
    End If
End Sub

Public Function FileInUse(sFileName As String) As Boolean
On Error Resume Next
Open sFileName For Binary Access Read Lock Read As #1
Close #1
FileInUse = IIf(Err.Number > 0, True, False)
On Error GoTo 0 
End Function

The file test function is courtesey of user2267971 answering this question also on how to test if a file is open