Automate Updating Links to External WorkBooks

[Igor R.]

Hello!
I have a lot of files that has links to data in source files.
Those source files change location and names monthly or quarterly, mostly by adding month and year to the directory name and file name as shown in example:
1. Old File - \\Server\2022-12\File_1222.xlsx
1. New File - \\Server\2023-01\File_01223.xlsx

I am looking for a way to update links (Data - Edit Links) in my destination files to source files by formula or VBA code automatically.
Today I have to do this manually. file by file, and it can take hours cause of I have a lot of such files.

Thanks in advance for any good solution.

 

[Igor R.]

Note that you will need to modify the code to match the structure of your files and the desired updates you want to make.

Sub UpdateLinks()

Dim wb As Workbook
Set wb = ThisWorkbook

With wb.UpdateLinks
    .OnCalculation = xlManual
    .OnOpen = False
    .BackgroundQuery = False
    .Source = wb.LinkSources(Type:=xlExcelLinks)
    .LinkType = xlExcelLinks
End With

For Each lnk In wb.LinkSources(Type:=xlExcelLinks)
    wb.BreakLink Name:=lnk, Type:=xlExcelLinks
Next lnk

End Sub

Thank you,
But as far as I understand, the code you suggested breaks links and does not update them.
Can you give an explanation?