Using Macro to update Links

[Thom Ernest]

I would like change links using excel based on a value I put in a cell in a
master workbook.

I am having difficulty getting it to work, I am not a program writer, but a
recorder and then make modifications.

Dim ITOPacingOld
Set ITOPacingOld = Worksheets("Master").Range("E15")
Dim SelectITOPacingNew
Set SelectPartsPacingNew =
Worksheets("Master").Range("F14")ActiveWorkbook.ChangeLink
Name:=(ITOPacingOld), NewName:=(SelectITOPacingNew), Type:=xlExcelLinks

Where "ITOPacingOld" is the old link and "ITOPacingNew" is the new link.

Thanks.

 

[Bill Pfister]

Thom, is the ActiveWorkbook the file that currently contains the links?

Here is the code that I use for changing links, slightly modified for your
situation.



Sub Fill()
Dim ITOPacingOld
Dim SelectITOPacingNew

Set ITOPacingOld = Worksheets("Master").Range("E15")
Set SelectPartsPacingNew = Worksheets("Master").Range("F14")

Call ChangeLinks(SelectPartsPacingNew.Value, ITOPacingOld.Value)

End Sub



' Change links from strWorkbookOld to strWorkbookNew workbook
Public Sub ChangeLinks(strWorkbookNew As String, strWorkbookOld As String)
Dim wbk As Object

On Error GoTo ErrorHandler

' Assumes that new workbook is open
Set wbk = Application.Workbooks(strWorkbookNew)

If (Not (IsEmpty(wbk.LinkSources(xlExcelLinks)))) Then
wbk.ChangeLink Name:=strWorkbookOld, NewName:=strWorkbookNew,
Type:=xlExcelLinks
End If

Exit Sub

ErrorHandler:
MsgBox Err.Number, Err.Description, "ChangeLinks"
Exit Sub
Resume
End Sub


Let me know if this works for you.

Regards,
Bill