Change externally referenced link by changing cell

[kgartin]

I have a workbook with a ton of external links (Tabs JAN - DEC each with at least 100 external cell references). The workbook it's linked to changes annually. Instead of going through the LOOOOOONG process of editing the links via the DATA tab, is there a quicker way to do this? It used to take over an hour to accomplish (not sure now since I've upgraded computer and software). I don't want to go through the process of testing if there's a quicker way.

The name of the linked workbook includes the year (2018 COMMERCIAL FORECAST) which is the only value that changes in the name so I'd like to be able to type the new year in a cell and all links would be automatically changed.

 

[kgartin]

Bump......................

 

[John_w]

I assume you are changing the links via Data tab -> Edit Links. This macro does the same - try running it on a copy of your workbook.

Public Sub Change_Link_Sources()

    Dim wb As Workbook
    Dim links As Variant, i As Long
    Dim currentExternalFile As String, newExternalFile As String
    
    currentExternalFile = "2018 COMMERCIAL FORECAST"
    newExternalFile = "2019 COMMERCIAL FORECAST"
    
    Set wb = ActiveWorkbook 'or ThisWorkbook
    
    links = wb.LinkSources(xlExcelLinks)

    For i = 1 To UBound(links)
        wb.ChangeLink links(i), Replace(links(i), currentExternalFile, newExternalFile), xlLinkTypeExcelLinks
    Next
    
End Sub

Once you've confirmed it works we can modify the code to change the links automatically when you type the new year in a cell. For this, can you confirm that the linked file name begins with '2018 COMMERCIAL FORECAST'.