Renaming excel file without breaking links to other excel files

[Peter Davison]

Hi,
I have a master excel file that I want to rename, however I have four other files with many thousands of formulas that relate back to my master file.
Is there a way I can rename my master file without affecting all the formulas in my other four files?
Any help would be appreciated.
Thanks

 

[offthelip]

You could try something like this which is untested, this does rely on both files existing though so preobably need s tweaking

Sub tst()
Dim ws As Worksheet

strk = "='C:\Documents and Settings\username\filename.xlsm'!"
strk2 = "='C:\Documents and Settings\username\newfilename.xlsm'!"
For Each ws In ActiveWorkbook.Worksheets
    
    row_min = ws.UsedRange.Row
    row_max = row_min + ws.UsedRange.Rows.Count - 1
    col_min = ws.UsedRange.Column
    col_max = col_min + ws.UsedRange.Columns.Count - 1

inarr = Range(Cells(row_min, col_min), Cells(row_max, col_max)).Formula
ilim = UBound(inarr, 1)
jlim = UBound(inarr, 2)

For i = 1 To ilim
For j = 1 To jlim
   forstr = CStr(inarr(i, j))
     If forstr <> "" Then
        MMn = InStr(forstr, strk)
        If MMn > 0 Then
         inarr(i, j) = Replace(forstr, strk, strk1)
        End If
     End If
 Next j
 Next i
Range(Cells(row_min, col_min), Cells(row_max, col_max)).Formula = inarr
Next ws

End Sub

 

[Peter Davison]

Hi,
I have a master excel file that I want to rename, however I have four other files with many thousands of formulas that relate back to my master file.
Is there a way I can rename my master file without affecting all the formulas in my other four files?
Any help would be appreciated.
Thanks

 

[Peter Davison]

Thank you for your help, I will try this over the weekend.
Most appreciated