Macro: Edit link on behalf of Cell Information

[lnkinho]

Hi

Is it possible to edit link via Macro with information from a specific cell?

I want to change the excelfile name "110.xlsx" via a macro. So I thought I can put the filename in cell B1, lets say "109".

So when I click on the macro: either the macro can seach for 110 and replace all with B1, or change the name of the link. Not sure if either way is possible?

"=IFERROR('C:\Users\XX\Desktop\XX\cashflow\[110.xlsx]Contract Template'!$B2;0)"

Got any tips?

 

[lnkinho]

Even if I only have "=INDIRECT(B1)", it still shows #REF!.. Hm...

 

[Arithos]

Meaning like this,

=Indirect("[" & B1 & ".xlsx]Contract Template'!$B4")

This works for me, and should work for you aswell. if you get a Ref here, the workbook is named incorrectly in cell B1, or the workbook is not open.

 

[lnkinho]

Hm.. Doesnt work here..

Cell B1: 109.

Workbook is open..

 

[Arithos]

Even if I only have "=INDIRECT(B1)", it still shows #REF!.. Hm...

=Indirect(B1) is supposed to give you a #REF!

You could use this(where cell A1 is some sheetname):
=INDIRECT(A1 &"!B1")

 

[Arithos]

Hm.. Doesnt work here..

Cell B1: 109.

Workbook is open..

do you have a "." behind 109?

 

[lnkinho]

No, sorry. Only "109".
Do I have to make sure that the cell is a number or anything else?

 

[Arithos]

This works for me:
Where A1 refers to a workbook I have opened, a macro workbook in this case.

=INDIRECT("[" & A1 &".xlsm]Sheet1!$C18")

The contents of the cell does not need to be anything specific, it just needs to match the name of the workbook, it can contain numbers or text.

You should try using google for information on new formulas, look at this one if you are still having trouble.

 

[Darren Bartrup]

This page discussed getting INDIRECT to work with closed workbooks:
Excel “Pull” Function: Creating dynamic links to closed workbooks | Numbermonger

Failing that, this code might help (I haven't fully tested it though):
In a normal module:

'--------------------------------------------------------------------------------------
' Procedure : ChangeLink
' Purpose   : Updates external links.
'---------------------------------------------------------------------------------------
Public Sub ChangeLink(FromLink As Workbook, ToLink As Workbook)
    Dim arrLinks As Variant
    
    On Error GoTo ERROR_HANDLER


    arrLinks = ThisWorkbook.LinkSources(xlExcelLinks)
    If IsEmpty(arrLinks) Then
        MsgBox ToLink.Name & " does not contain any links.", vbOKOnly, "No External Links"
        Exit Sub
    End If
    
    ThisWorkbook.ChangeLink Name:=FromLink.Name, _
                   NewName:=ToLink.FullName, Type:=xlLinkTypeExcelLinks


    On Error GoTo 0
    Exit Sub


ERROR_HANDLER:
    Select Case Err.Number
        
        Case Else
            MsgBox "Error " & Err.Number & vbCr & _
                " (" & Err.Description & ") in procedure ChangeLink." & vbCr & vbCr & _
                    "Please contact the spreadsheet designer."
            Err.Clear
            Application.EnableEvents = True
    End Select
                   
End Sub

and then in the worksheets module:

Private sChangeFrom As String


Private Sub Worksheet_Change(ByVal Target As Range)


    Dim sDirectory As String
    Dim wrkBkFrom As Workbook
    Dim wrkBkTo As Workbook
    
    Application.EnableEvents = False
    
    If Target.Address = "$B$1" Then
        sDirectory = "C:\Documents and Settings\user\Desktop\"
    
        If Dir(sDirectory & sChangeFrom) <> "" And _
           Dir(sDirectory & Target.Value) <> "" And _
           sChangeFrom <> Target.Value Then
           
            Set wrkBkFrom = Workbooks.Open(sDirectory & sChangeFrom, False, True)
            Set wrkBkTo = Workbooks.Open(sDirectory & Target.Value, False, True)
    
            ChangeLink wrkBkFrom, wrkBkTo
            
            wrkBkFrom.Close False
            wrkBkTo.Close False
        End If
    End If
    
    Application.EnableEvents = True
    
End Sub


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Address = "$B$1" Then
        sChangeFrom = Target.Value
    End If
End Sub

 

[Arithos]

@Darren

Interesting, I will check this out for later use for myself aswell.

 

[lnkinho]

Hm, this is far too advanced for me. Any chance in help?