Forum Discussion
How to change Excel links in bulk by recognizing file name
Dear Team,
I am now preparing monthly budget-forecast review workbook "Jan forecast.xls" (Destination workbook).
In order to prepare this, I will use numbers linked to several workbooks "Jan exp.xls", "Jan cost.xls" (source workbooks) provided on a monthly basis.
Since there are 30+ links in "Jan forecast.xls" (Destination workbook) and I'd like to maintain the formulas based on the current destination workbook but only update the links for next month's source numbers.
This is to seek any kind advice/methods to update the source links according to the file name (from"Jan exp.xls" to "Feb exp.xls"/ from"Jan cost.xls" to "Feb cost.xls"...) automatically by using macro on a monthly basis instead of manually changing the 30+ links via Data-> Edit Links function?
3 Replies
AngeloRussel Have you tried Excell's built-in tool "Find and Replace" option, Ctrl-H?
Fill in Jan exp in the Find what: field
Fill in Feb exp in the Replace with: field
Repeat this for the other file names in your links.
Under Optons tell it to apply the find & replace to the entire workbook. That should do it.
Riny_van_Eekelen Hi Riny, thank you for the response! Yes, I've tried the "Find and Replace" option but since the file name (ex. Jan exp) is also in the spreadsheets but I dont want to update it to Feb exp. Thus, this is to seek if there's any other tools/ methods can update the links in a batch (ex. replace bunch of old links to new links) instead of using the Data->Edit link method? thank you.
AngeloRussel Would it work if you use Jan exp.xlsx ?
Or, if your sheet contains a cell with a text like, "Jan expenses", consider entering "Expenses January" in stead. Just so that the actual file names used in the links are not used as a text in the sheet as well.
