Excel - Code to change part of a formula across worksheets

Asked By BeSmart

31-Jan-10 11:03 PM

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 is looking
for Hyperlinks, not formula links and therefore it is not working - but I
understand the logic of what it is 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

01-Feb-10 12:06 AM

I am not clear id there is just one workbook you need to change, or more than
one.

The example below will just operate on the activeworkbook, but it could be
modified to loop through all open workbooks.

Tim


Sub UpdateLinks()

Dim aWorkbook As Excel.Workbook
Dim aWorksheet As Excel.Worksheet
Dim OldFile As String
Dim TargetFile As String
Dim rngFormulas As Range, c As Range

Set aWorkbook = ActiveWorkbook

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

Application.Calculation = xlCalculationManual

For Each aWorksheet In aWorkbook.Worksheets

Set rngFormulas = aWorksheet.Cells.SpecialCells(xlCellTypeFormulas)
For Each c In rngFormulas
c.Formula = Replace(c.Formula, OldFile, TargetFile)
Next c

Next aWorksheet

Application.Calculation = xlCalculationAutomatic


End Sub

01-Feb-10 12:28 AM

Give this code a try (it works on the active workbook only, but that can be
changed if needed)...

Sub ReplaceWorkbookName()
Dim OldFile As String, TargetFile As String, WS As Worksheet
OldFile = InputBox("Enter Old File Name")
TargetFile = InputBox("Enter New File Name")
On Error Resume Next
Application.DisplayAlerts = False
For Each WS In ThisWorkbook.Worksheets
WS.Cells.SpecialCells(xlCellTypeFormulas).Replace What:=OldFile, _
Replacement:=TargetFile, LookAt:=xlPart, MatchCase:=False
Next
Application.DisplayAlerts = True
End Sub

--
Rick (MVP - Excel)

01-Feb-10 07:06 PM

Thanks Tim & Rick
(FYI - yes it is for one workbook - the active workbook. Sorry I did not
include that in my details...)

However I am still being asked to select the file (in a File/Open) window for
every formula it finds...

Application.DisplayAlerts = False
This is in the code and it stops the error message from appearing, but then
the cell result is #REF and when you click into the cell it again asks you to
select the file in the window....

Any suggestions on how to solve this?
--
Thank for your help
BeSmart

01-Feb-10 07:19 PM

Cancel that....
I am a twit!!!! (I did not save the new file to xls so it could not find it)....
Tim and Rick are genius!!!!!
I only hope to one day aspire to knowing how to get Excel to do what I want
like you guys.
--
Thank VERY MUCH for your help
BeSmart