May 30 2022 02:51 AM
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.
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.
May 30 2022 10:21 AM
Are the filenames part of cell formulas?
May 30 2022 10:52 AM
Yes they are.
May 30 2022 11:09 AM
In that case, I don't understand why the code doesn't work...