Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code to change part of a formula across worksheets
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 -- Thank for your help BeSmart |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code to change part of a formula across worksheets
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" wrote in message ... 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 -- Thank for your help BeSmart |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code to change part of a formula across worksheets
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" wrote in message ... 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 -- Thank for your help BeSmart |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code to change part of a formula across worksheets
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" wrote: 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" wrote in message ... 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 -- Thank for your help BeSmart . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code to change part of a formula across worksheets
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" wrote: 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" wrote: 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" wrote in message ... 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 -- Thank for your help BeSmart . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
change part of hyperlink formula | Excel Discussion (Misc queries) | |||
change color of all cells with formula or are part of a formula | Excel Discussion (Misc queries) | |||
Using formula in code...Part 3! | Excel Programming | |||
Using formula in code....Part 2! | Excel Programming | |||
Change part of formula | Excel Worksheet Functions |