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

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

Hi Jeff
Thanks for your code - however I'm not using "Hyperlinks"...
I have formulas that include links 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 XP version.xls]Detailed Report'!$U$18+$C83

The macro needs to prompt the user to enter the OLD workbook name (above) as 
and then 
enter the new workbook name e.g. "[XClient Plan for Restructuring and 
Rebudgeting as at 07_10_09 XP version.xls]" (the date changed in the 
filename).

Nothing else changes changes in the formula.

With the code I used before wanted the user to select the file and sometimes 
the worksheet as well - for every worksheet...

We only want to change the workbook name once (when the user enters the two 
names into the input boxes) - without having to also navigate through to the 
actual file in a FILE/OPEN window for each worksheet.

Hopefully this will only be a small change to the code you have already 
written.
-- 
Thank again for your help - I really appreciate it.
BeSmart


"Jeff" wrote:

> 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