Changing automatically several links on several files in Excel

[lndayong]

Hello Everyone, 

I am trying to update a bulk of Excel files and each has about 30 links to file from the previous quarter, say "122021". How do I update this automatically, each quarter (first on one file, and then on the other files with same structure) to say "032022" maybe using a VBA code? The Find and replace option still requires changing one after another and not on the bulk of all files.

As an example, we can have the files Expense_062022 and cost_062022 and I would change all links in this 2 files automatically to pick up previous quarter items from Expense_032022 and cost_062022. At the moment, the previous files are mapped to Expense_122021 and cost_122021. 

Expense and cost have exactly the same file structure. 

Thank you for your help.  

[#2]

@lndayong 

Perhaps a macro. It assumes that the workbooks to be updated are in a single folder, with no other workbooks.

Sub MultiUpdate()
    ' Path must end in \
    Const strPath = "C:\Excel\"
    ' Change as needed
    Const strFrom = "122021"
    Const strTo = "032022"
    Dim strFile As String
    Dim wbk As Workbook
    Dim wsh As Worksheet
    Application.ScreenUpdating = False
    strFile = Dir(strPath & "*.xls*")
    Do While strFile <> ""
        Set wbk = Workbooks.Open(Filename:=strPath & strFile, UpdateLinks:=False)
        For Each wsh In wbk.Worksheets
            wsh.Cells.Replace What:=strFrom, Replacement:=strTo, LookAt:=xlPart
        Next wsh
        wbk.Close SaveChanges:=True
        strFile = Dir
    Loop
    Application.ScreenUpdating = True
End Sub

[#3]

@Hans Vogelaar 

Thank you for your response. I however tried this and it did not work for me. Maybe I didnt explain properly my problem, apologies. 

In each file, I have several links pointing to diffferent files with a part of the name being 122021. These file have the same structure as the file with name 032022. As I am trying to roll forward the 032022 file to 062022, I need to change all the links with name 122021 to 032022. 

This roll forward to be done for about 15 files of similar structure (with differing links)

Please if you have any proposals, it will be much appreciated. 

[#4]

@lndayong 

Are the filenames part of cell formulas?

[#5]

@Hans Vogelaar 

Yes they are.

[#6]

@lndayong 

In that case, I don't understand why the code doesn't work...