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

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.

Answer 2

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

Answer 3

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

Answer 4

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

Answer 5

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.