Hi all - I hope someone can help with this...

I have formulas that include links (not hyperlinks) in their calculations e.g.
Cell A5 formula is:

='C:\Documents and Settings\Bob.JOB-Bob\My Documents\Bob
Personal\Inventors\Meetings\[XClient Plan for Restructuring and Rebudgeting
as at 06_10_09.xls]Detailed Report'!$U$18+$C83

I need a macro to prompt the user to enter the OLD workbook name into an
input box e.g. [XClient Plan for Restructuring and Rebudgeting
as at 06_10_09.xls]

The code looks for all cells with that string and then replaces it with the
new workbook name (entered by the user into an input box e.g. "[XClient Plan
for Restructuring and Rebudgeting as at 07_10_09.xls]"
(Note - the date changed in the filename).

Nothing else changes in the formulas that the system finds.

I was using the following code - but it asks the user to:

1. Enter the old and new files names for every worksheet (I have 9
worksheets)
AND
2. Select the workbook and worksheet name in a File/Open window for every
worksheet.

Is there a way to reduce the nominating process i.e. the user only has to
enter the old and new workbook name only once and it apply to all worksheets
without File/Open windows appearing being required?

Earlier I got some help from "Jeff " (the following code) - but it's looking
for Hyperlinks, not formula links and therefore it isn't working - but I
understand the logic of what it's doing... Can I change it from looking for
hyperlinks to looking for links to external workbooks in a formula? :

Sub UpdateLinks()

Const FolderPath As String = "\\NetworkShare\YourFolder\YourSubfolder\"
Dim aWorkbook As Workbook
Dim aWorksheet As Variant
Dim OldFile As String
Dim TargetFile As String
Dim Link As Hyperlink

Set aWorkbook = ThisWorkbook

OldFile = InputBox("Enter Old File Name")
TargetFile = InputBox("Enter New File Name")

''''Loop through worksheets
For Each aWorksheet In aWorkbook.Worksheets
'''''Loop through Hyperlinks in worksheet
For Each Link In Worksheets(aWorksheet.Name).Hyperlinks
''''''Check for oldFile Name
If InStr(1, Link.TextToDisplay, OldFile, vbTextCompare) 0 Then
With Link
'''''Update Hyperlink info
..Address = FolderPath + TargetFile
..TextToDisplay = FolderPath + TargetFile
End With
End If
Next
Next
End Sub

--
Thank for your help
BeSmart