May 30 2022 02:51 AM
May 30 2022 02:51 AM
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.
May 30 2022 03:21 AM
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
May 30 2022 10:18 AM
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.