2010 excel VBA changing links to external excel files
I very new to VBA and trying to work a way in VBA (2010 excel) to perform the equivalent of "edit links". In my macro I search the formulas for the telltale start [ and end ] of an external link and trying to do a bulk replacement to a new external link using:
Cells.Formula.Replace What:=oldlink_filename, Replacement:=newlink_filename, LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
Excel errors saying "run-time error 7 -- Not enough memory". The file I'm working on is ~5000 rows and has variable number of columns (~2-10) with the external link references in them. I wouldn't have thought this would be a memory issue (and the manual edit links does it no problems) so I'm assuming I using an inefficient or incorrect method to try and do the formula replacement. Any ideas how to do it?
ALSO I set Applciation.DisplayAlerts to false and when the macro opens the file with external links it displays a dialog box telling me there are links and do I want to update them or not. How can I turn off this request (and tell it I don't want the data updated)?
Any help greatly appreciated. Assume I know very little VBA in your answers!!