Hi BeSmart, If I completely understand you want to loop though the workbook
adn each sheet in that workbook updating your hyperlinks. Right? If thats the
case try somthing like this.

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 OldFile Name")
TargetFile = InputBox("Enter 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


"BeSmart" wrote:

Hi
I just found the answer to "Find and Replace in VBA" and tried to adapt it
for what I'm doing, but it's more difficult so I need some help please.

I have multiple worksheets in an excel 2007 workbook - a monthly report.
Within the worksheets I have many cells that link to last months workbooks -
(i.e I compare last month figures to this months figures).

I need the macro to do the following:

- Create an Input box for the user to "enter the name ofTHE OLD months
workbook"

- Create an Input box for the user to "enter the name of THE NEW months
workbook"

- Excel finds cells with links containing the OLD workbook name and replaces
it with the NEW workbook name

The problem:

After the user enters the NEW name excel asks the user to also select the
new workbook from a window and sometimes requires the worksheet to be
selected as well.

Is there any way to stop this requirement, but to ensure that the revised
links work?

I tried including "Application.ScreenUpdating = False" and
"Application.DisplayAlerts = False", but then the revised formulas turned to
#REF once all of the old & new workbook names had been entered (9 times each
for 9 worksheets).

Also - is there a way to enter the Old and New workbook names once instead
of once for every worksheet in the workbook?

Old worksheet name:
.../[Plan for Restructuring and Rebudgeting as at 06_10_09 XP
version.xls]/Monthly Detailed P&L

New worksheet name:
.../[Plan for Restructuring and Rebudgeting as at 07_10_09 XP
version.xls]/Monthly Detailed P&L

No other details in the links change (i.e. locations like the folder names,
worksheet name and cell etc are in exactly the same place in the new workbook
so they don't need to change)

Current code:

Sub Replace()
Dim WS As Worksheet

Application.ScreenUpdating = False
Application.DisplayAlerts = False

For Each WS In Worksheets
WS.Cells.Replace What:=InputBox("Enter prior workbook words to be replaced
in links"), Replacement:=InputBox("Enter new workbook words to be entered
into links"), _
LookAt:=xlPart, SearchOrder:=xlByColumns, _
MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
Next

Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub


--
Thank for your help in Advance
BeSmart