Code to change part of a formula across worksheets

B

BeSmart

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
 
T

Tim Williams

I'm not clear id there's 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




BeSmart said:
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
Click to expand...
 
R

Rick Rothstein

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)


BeSmart said:
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
Click to expand...
 
B

BeSmart

Thanks Tim & Rick
(FYI - yes it's for one workbook - the active workbook. Sorry I didn't
include that in my details...)

However I'm 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


Rick Rothstein said:
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)


BeSmart said:
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
Click to expand...

.
Click to expand...
 
B

BeSmart

Cancel that....
I am a twit!!!! (I didn't save the new file to xls so it couldn't 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


BeSmart said:
Thanks Tim & Rick
(FYI - yes it's for one workbook - the active workbook. Sorry I didn't
include that in my details...)

However I'm 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


Rick Rothstein said:
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)


BeSmart said:
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
Click to expand...

.
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

VBA to find filename and replace 2
VBA - exporting serial letters to single documents 0
potential bug in copy method 3
how do i loop thru all the worksheets in 2 files one by one 0
Change a great number of words in word files, with a macro as short as possible. 5
Excel crashes and restarts when accessing newly created worksheet 2
Import Worksheets and "Overright" 9
Password Cracker for Excel Worksheets/ Workbooks 9

Top