Method 'ChangeLink of Object'_Workbook' failed

[smporco]

I've been struggling all day...searching the forum for answers with no luck! The last line below in the code errors with
the message Method 'ChangeLink of Object'_Workbook' failed
Appreciate you heros out there!




Sub SETUP2()

'set stop watch for timing macro elapsed time
Dim myTime As Date, elapsedTime As Date, dblDuration As Double
myTime = Time

Dim source As Workbook
Dim Prior, PCurrent, CrYear, CurrMonth, thiswkb As String
Dim OldLnkPrior, OldLnkCur, NewLnkPrior, NewLnkCur As String
Prior = ActiveSheet.Range("AB3")
PCurrent = ActiveSheet.Range("AB2")
CrYear = ActiveSheet.Range("AB4")
CurrMonth = ActiveSheet.Range("AB1")
NewLnkCur = "\\usnvfsa0\Accounting$\Office Records\Treasury\Balancing\" & CurrMonth & "\Balancing Data " & CurrMonth & ".xlsx"
OldLnkCur = "\\usnvfsa0\Accounting$\Office Records\Treasury\Balancing\" & CurrMonth & "\Balancing Data " & PCurrent & ".xlsx"
NewLnkPrior = "\\usnvfsa0\Accounting$\Office Records\Treasury\Balancing\" & CrYear & "\" & PCurrent & "\Balancing Data " & PCurrent & ".xlsx"
OldLnkPrior = "\\usnvfsa0\Accounting$\Office Records\Treasury\Balancing\" & CrYear & "\" & Prior & "\Balancing Data " & Prior & ".xlsx"
thiswkb = "Contracts Balancing " & CurrMonth

Application.ScreenUpdating = False

'to save macro performance run time, open current month Balancing Data source
Set source = Workbooks.Open(NewLnkCur)
Workbooks(thiswkb).Activate
ThisWorkbook.ChangeLink OldLnkCur, NewLnkCur, xlLinkTypeExcelLinks

[HaHoBe]

Hi, smporco,

please wrap your code with code-tags for better readability.

AFAIK you would need to run through an array of the links and change them one after the other, and I´m not really sure if you want it updated in the workbook with the code (ThisWorkbook) or the active one (ActiveWorkbook):

Please Login or Register  to view this content.

Ciao,
Holger

[smporco]

Thank you so much Holger! here's my working piece of code.

Please Login or Register  to view this content.