VBA runs very slow on changing external link

user_3094555 0
Viewed 1574

I have a excel file in which cell A1 contains the formula:

=C:\folder1\[1.xls]Sheet1!A1

and relatively for all cells up to H100.

Now I wish to change the formula for A1 to (similarly for other cells up to H100)

=C:\folderA\[A.xls]Sheet1!A1

Originally I recorded a macro to do this by a find and replace statement 

Sub replace()

    Application.EnableEvents = False
    Application.Calculation = xlCalculationManual

    Range("A1:H100").replace What:="folder1\[1.xls]", Replacement:="folderA\[A.xls]", _
       LookAt:= xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
       ReplaceFormat:=False

    Application.EnableEvents = True

End Sub

But it takes over 10 minutes to do that and on the bottom-left corner of excel, it says "Link: ---" (loading...)

Are there any alternative method?

vba excel
1 Answers
Related