Excel - VBA to find filename and replace

Asked By BeSmart

27-Jan-10 03:16 AM

Hi
I just found the answer to "Find and Replace in VBA" and tried to adapt it
for what I am doing, but it is 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
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 do not 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

27-Jan-10 09:27 AM

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

28-Jan-10 03:15 AM

Hi Jeff
Thanks for your code - however I am 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