How to access Excel Link using a unique identifier in VBA

Question

How can I reference external books in VBA using a unique identifier that does not change when a file is opened? It works fine when the full path to the file is included, and the file with the same name is not open. However, when the file is open, the full form with the file track does not work and one file name does not work.

I wanted to create a Sub update to update all links, and it lures if the spreadsheet is open (see paragraph 2 below).

Here are some reasons why I think it should be possible:

• It seems that in the manual link update menu there is only the name of the file to be referenced;
• Also, you cannot open two books with the same name, and thus, if you open the original link, cell links change from the file path (and this is what causes the problem.

This code that I currently have updCellRefis a link to the file path cell (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, it occurred when I used the above function to update the values, however, when the original spreadsheet was opened, only its file name refers to it. When it is closed, the full path to the file refers to it.

I am using Excel Professional 2010 v14 with VBA v7.0

Note. I do not want to use any other software, including Power Query, since it cannot be installed without administrator rights.

Answer 1

, , Excel, , . , " VBA, ", , , , .

MS Office Support, , " , "

: , , :

• , .
• , , ,

:

 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

2267971, , ,

Answer 2

.

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

link, ,

"" > " ", ?

3 , .

Answer 3

, :

1. , , , , ; , , , - , - , ( , ). excel, , ​​vba, , , - , , -, , , , ( , , /). , VBA. , -

    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. (1) , , WB ( , , )

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

-

• ,
• , ChangeLink, Excel
• , , .

 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

, , , , - , - :

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

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

, , , . , - , , . , .

, , ADO # MS Access, , Excel. , , . , ADO , , .